SQL Triggers

Triggers in SQL are essentially automated actions that the database can take in response to certain events. They are special types of stored procedures and are executed automatically when specific events, like INSERT, UPDATE, or DELETE, occur in the database.

Understanding Triggers

Here's why triggers are useful:

• Data Integrity: They can enforce business rules and data integrity by automatically checking or modifying data in response to database changes.
• Automation: Triggers can automatically populate or update columns based on changes in other columns or tables.
• Notification: They can provide notifications or log events when certain data operations occur.

Example 1: Logging Salary Updates

In this scenario, we want to maintain an audit trail for changes to an "Employees" table. When there's an update to the "Salary" column, the change gets logged into a "SalaryAudit" table.

                            CREATE TRIGGER tr_AfterUpdateSalary
                            ON Employees
                            AFTER UPDATE
                            AS
                            BEGIN
                                IF UPDATE(Salary)
                                BEGIN
                                    INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, AuditDate)
                                    SELECT d.EmployeeID, d.Salary, i.Salary, GETDATE()
                                    FROM inserted i
                                    JOIN deleted d ON i.EmployeeID = d.EmployeeID;
                                END
                            END;
                        

Example 2: Preventing Data Deletion

Assume you want to ensure that no records are accidentally deleted from the "Products" table. A trigger can be used to prevent such actions.

                            CREATE TRIGGER tr_PreventDeleteProducts
                            ON Products
                            INSTEAD OF DELETE
                            AS
                            BEGIN
                                PRINT 'Deletion not allowed on Products table!';
                                ROLLBACK TRANSACTION;
                            END;
                        

Example 3: Automatically Updating Timestamps

For a "Comments" table, every time a comment is updated, you might want to automatically update a "LastModified" timestamp column.

                            CREATE TRIGGER tr_UpdateCommentTimestamp
                            ON Comments
                            AFTER UPDATE
                            AS
                            BEGIN
                                UPDATE Comments
                                SET LastModified = GETDATE()
                                WHERE CommentID IN (SELECT CommentID FROM inserted);
                            END;
                        

Conclusion

Triggers offer an automated and robust way to enforce rules, react to data changes, and maintain data integrity within SQL databases. By understanding and utilizing triggers effectively, developers can create more reliable and self-sustaining database applications.