Trigger Body: the SQL statements to execute, enclosed in BEGIN \u2026 END.<\/span><\/li>\n<\/ul>\nFor example, a BEFORE INSERT trigger runs just before a new row is added to the table, and an AFTER UPDATE trigger runs right after an existing row is changed. MySQL requires the keyword FOR EACH ROW in a trigger, which makes it execute the trigger body for every row affected by the operation.<\/span><\/p>\nInside a trigger, you refer to the row data using the NEW and OLD aliases. In an INSERT trigger, only NEW.column is available (the incoming data). Similarly, in a DELETE trigger, only OLD.column is available (the data about the row being deleted). However, in an UPDATE trigger, you can use both: OLD.column refers to the row\u2019s values before the update, and NEW.column refers to the<\/span> values after the update.<\/span><\/p>\nLet\u2019s see trigger SQL syntax:<\/span><\/p>\nCREATE TRIGGER trigger_name<\/span>\nBEFORE|AFTER {INSERT|UPDATE|DELETE} ON table_name<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n-- SQL statements here --<\/span>\nEND;<\/span><\/code><\/pre>\nThis is the standard SQL form. One thing which needs to be noted is that the trigger bodies often include multiple statements with semicolons; you should usually change the SQL delimiter first, for example to \/\/, so the whole CREATE TRIGGER block is parsed correctly.<\/span><\/p>\nStep-by-Step Example of Creating Triggers<\/span><\/h2>\nNow let\u2019s see how we can create triggers in SQL.<\/span><\/p>\nStep 1: Prepare a Table<\/span><\/h3>\nFor this, let\u2019s just create a simple users table:<\/span><\/p>\nCREATE TABLE users (<\/span>\nid INT AUTO_INCREMENT PRIMARY KEY,<\/span>\nusername VARCHAR(50),<\/span>\ncreated_at DATETIME,<\/span>\nupdated_at DATETIME<\/span>\n);<\/span><\/code><\/pre>\nStep 2: Change the Delimiter<\/span><\/h3>\nIn SQL, you can change the statement delimiter so you can write multi-statement triggers. For example:<\/span><\/p>\nDELIMITER \/\/<\/span><\/code><\/pre>\nStep 3: Write the CREATE TRIGGER Statement<\/span><\/h3>\nFor instance, we can create a trigger that sets the created_at column to the current time on insertion:<\/span><\/p>\nCREATE TRIGGER before_users_insert<\/span>\nBEFORE INSERT ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\nIF NEW.created_at IS NULL THEN<\/span>\nSET NEW.created_at = NOW();<\/span>\nEND IF;<\/span>\nEND;<\/span>\n\/\/<\/span><\/code><\/pre>\nSo, in the above code, the BEFORE INSERT ON users means the trigger fires before each new row is inserted. The trigger body checks if NEW.created_at is null, and if so, fills it with NOW(). This automates setting a timestamp.<\/span><\/p>\nAfter writing the trigger, you can restore the delimiter if desired so that other codes can execute without any issues.<\/span><\/p>\nDELIMITER ;<\/span><\/code><\/pre>\nStep 4: Test the Trigger<\/span><\/h3>\nNow, when you insert without specifying created_at, the trigger will be set automatically.<\/span><\/p>\nINSERT INTO users (username) VALUES ('Alice');<\/span>\nSELECT * FROM users;<\/span><\/code><\/pre>\nAnd the created_at will be filled automatically with the current date\/time. A trigger can automate tasks by setting up default values.<\/span><\/p>\nDifferent Types of Triggers\u00a0<\/span><\/h2>\nThere are six types of SQL triggers for each table:<\/span><\/p>\n\n- BEFORE INSERT trigger<\/span><\/li>\n
- BEFORE UPDATE Trigger<\/span><\/li>\n
- BEFORE DELETE Trigger<\/span><\/li>\n
- AFTER INSERT Trigger<\/span><\/li>\n
- AFTER UPDATE Trigger<\/span><\/li>\n
- AFTER DELETE Trigger<\/span><\/li>\n<\/ol>\n
Let\u2019s learn about each of them through examples.<\/span><\/p>\n1. BEFORE INSERT Trigger<\/span><\/h3>\nThis trigger is activated before a new row is inserted into a table. It is commonly used to validate or modify the data before it is saved.<\/span><\/p>\nExample of trigger SQL syntax for BEFORE INSERT:<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER before_insert_user<\/span>\nBEFORE INSERT ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0SET NEW.created_at = NOW();<\/span>\nEND;<\/span>\n\/\/<\/span>\u00a0\n\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger is automatically set at the created_at timestamp to the current time before a new user record is inserted.<\/span><\/p>\n2. BEFORE UPDATE Trigger<\/span><\/h3>\nThis trigger is executed before an existing row is updated. This allows for validation or modification of data before the update occurs.<\/span><\/p>\nExample of trigger SQL syntax for BEFORE UPDATE:<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER before_update_user<\/span>\nBEFORE UPDATE ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0IF NEW.email NOT LIKE '%@%' THEN<\/span>\n\u00a0\u00a0\u00a0\u00a0SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';<\/span>\n\u00a0\u00a0END IF;<\/span>\nEND;<\/span>\n\/\/<\/span>\n\u00a0\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger checks if the new email address is valid before updating the user record. If not, then it raises an error.<\/span><\/p>\n3. BEFORE DELETE Trigger<\/span><\/h3>\nThis is executed before a row is deleted. And can also be used for enforcing referential integrity or preventing deletion under certain conditions.<\/span><\/p>\nExample of trigger SQL syntax for BEFORE DELETE:<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER before_delete_order<\/span>\nBEFORE DELETE ON orders<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0IF OLD.status=\"Shipped\" THEN<\/span>\n\u00a0\u00a0\u00a0\u00a0SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders';<\/span>\n\u00a0\u00a0END IF;<\/span>\nEND;<\/span>\n\/\/<\/span>\n\u00a0\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger prevents deletion of orders that have already been shipped.<\/span><\/p>\n4. AFTER INSERT Trigger<\/span><\/h3>\nThis trigger is executed after a new row is inserted and is often used for logging or updating related tables.<\/span><\/p>\nExample of trigger SQL syntax for AFTER INSERT<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER after_insert_user<\/span>\nAFTER INSERT ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0INSERT INTO user_logs(user_id, action, log_time<\/span>\n\u00a0\u00a0VALUES (NEW.id, 'User created', NOW());<\/span>\nEND;<\/span>\n\/\/<\/span>\n\u00a0\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger logs the creation of a new user in the user_logs table.<\/span><\/p>\n5. AFTER UPDATE Trigger<\/span><\/h3>\nThis trigger is executed after a row is updated. And is useful for auditing changes or updating related data.<\/span><\/p>\nExample of trigger SQL syntax for AFTER UPDATE<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER after_update_user<\/span>\nAFTER UPDATE ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0INSERT INTO user_logs(user_id, action, log_time)<\/span>\n\u00a0\u00a0VALUES (NEW.id, CONCAT('User updated: ', OLD.name, ' to ', NEW.name), NOW());<\/span>\nEND;<\/span>\n\/\/<\/span>\n\u00a0\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger logs the change in a user\u2019s name after an update.<\/span><\/p>\n6. AFTER DELETE Trigger<\/span><\/h3>\nThis trigger is executed after a row is deleted. And is commonly used for logging deletions or cleaning up related data.<\/span><\/p>\nExample of trigger SQL syntax for AFTER DELETE<\/span><\/p>\nDELIMITER \/\/<\/span>\n\nCREATE TRIGGER after_delete_user<\/span>\nAFTER DELETE ON users<\/span>\nFOR EACH ROW<\/span>\nBEGIN<\/span>\n\u00a0\u00a0INSERT INTO user_logs(user_id, action, log_time)<\/span>\n\u00a0\u00a0VALUES (OLD.id, 'User deleted', NOW());<\/span>\nEND;<\/span>\n\/\/<\/span>\n\u00a0\nDELIMITER ;<\/span><\/code><\/pre>\nThis trigger logs the deletion of a user in the user_log table.<\/span><\/p>\nWhen and Why to Use Triggers<\/span><\/h2>\nTriggers are powerful when you want to automate things that happen when the data changes. Below are some use cases and advantages highlighting when and why you should use SQL triggers<\/span>.<\/p>\n\n- Automation of Routine Tasks:<\/b> You can automate or auto-fill or update columns like timestamps, counters, or some calculated values without writing any extra code in your app. Like in the above example, we have used the created_at and updated_at fields automatically.<\/span><\/li>\n
- Enforcing Data Integrity and Rules:<\/b> Triggers can help you check conditions and even prevent invalid operations. For instance, a BEFORE_INSERT trigger can stop a row if it breaks some rules by raising an error.\u00a0This makes sure that the data stays clean even if an error happens.<\/span><\/li>\n
- Audit Logs and Tracking:<\/b> They can also help you record changes automatically. An AFTER DELETE trigger can insert a record into a log table whenever a row is deleted. This gives an audit trail without having to write separate scripts.<\/span><\/li>\n
- Maintaining Consistency Across Multiple Tables:<\/b> Sometimes, you must have a situation where, when one table is changed, you want the other table to update automatically. Triggers can handle these linked updates behind the scenes.<\/span><\/li>\n<\/ul>\n