In SQL, a trigger is a special type of stored procedure that is automatically executed in response to certain events or actions, such as inserting, updating, or deleting data from a table. Triggers can be used to enforce business rules, ensure data consistency, or audit changes to the database. To create a trigger in SQL, you can use the CREATE TRIGGER statement followed by the trigger name, the trigger timing (BEFORE or AFTER), the trigger event (INSERT, UPDATE, or DELETE), and the name of the table or view that the trigger applies to. You can also specify the condition that triggers the execution of the trigger, and the actions that the trigger performs.
Here is an example of how to create a simple trigger in SQL:
CREATE TRIGGER myTrigger AFTER INSERT ON myTable FOR EACH ROW BEGIN -- Trigger actions here INSERT INTO myLog (event_time, event_type, event_data) VALUES (NOW(), 'INSERT', NEW.myColumn); END;
In this example, the trigger is named
myTrigger, and it is set to execute
INSERT event on the
myTable table. The
FOR EACH ROW clause indicates that the trigger should be executed once for each row that is affected by the trigger event.
The body of the trigger is enclosed in a
END block, and it contains the actions that the trigger performs. In this case, the trigger logs the event time, event type, and event data to a separate table named
NEW keyword refers to the newly inserted row in the
myTable table, and it allows you to access the values of the columns in the new row. You can also use the
OLD keyword to access the values of the columns in the old row, in case of an
By using triggers, you can automate tasks, enforce data integrity, and perform complex operations on your database. However, it is important to use triggers judiciously, as they can also affect database performance and scalability if not used properly.
For complete list of topic on DATA STRUCTURE AND ALGORITHM click hear