Home / Computer Science / SQL Server / SQL Trigger

SQL Trigger

In SQL, a trigger is a special type of stored procedure that is automatically executed in response to certain events, such as an INSERT, UPDATE, or DELETE operation on a table. Triggers are often used to enforce business rules or perform other automatic processing when data is changed in a database.

Create a trigger in sql

To create a trigger in SQL, you can use the CREATE TRIGGER statement, which has the following basic syntax:

CREATE TRIGGER trigger_name
ON table_name
WHEN (condition)
-- Trigger code goes here

Here’s a breakdown of the various parts of this syntax:

  • trigger_name: The name of the trigger you’re creating.
  • {BEFORE | AFTER}: Indicates when the trigger should be executed (before or after the triggering event).
  • {INSERT | UPDATE | DELETE}: Specifies the type of event that should trigger the execution of the trigger.
  • table_name: The name of the table on which the trigger is being created.
  • REFERENCING OLD AS old NEW AS new: Allows you to reference the old and new values of the rows being affected by the trigger.
  • FOR EACH ROW: Specifies that the trigger should be executed once for each row affected by the triggering event.
  • WHEN (condition): An optional condition that must be true for the trigger to execute.
  • BEGIN…END: The body of the trigger, which contains the code that should be executed when the trigger is activated.

For example, let’s say you want to create a trigger that automatically inserts a new row into a log table whenever a row is inserted into a users table. Here’s how you could do it:

CREATE TRIGGER insert_user_log
ON users
INSERT INTO log (user_id, action) VALUES (NEW.id, 'User created');

In this example, the insert_user_log trigger is set to execute AFTER INSERT on the users table. When the trigger is activated, it inserts a new row into the log table with the ID of the newly inserted row from the users table and a message indicating that a user was created.

About Santosh Kumar Gadagamma

I'm Santosh Gadagamma, an Experienced Software Engineer passionate about sharing knowledge in technologies like Java, C/C++, DBMS/RDBMS, Bootstrap, Big Data, Javascript, Android, Spring, Hibernate, Struts, and all levels of software design, development, deployment, and maintenance. I believe computers are essential for the world's functioning, and I'm committed to helping others learn the skills they need to succeed in tech. My website is a valuable learning tool to help you reach greater heights in your education and career, and I believe that education has no end points.

Check Also

Date and Time Functions in SQL Server

Introduction: In the world of databases, managing date and time is a crucial aspect. SQL …