Understanding Database Triggers: Syntax, Examples, and Business Rule Enforcement
Database triggers are essential event handlers in a DBMS that execute automatically in response to events such as INSERT, UPDATE, or DELETE. These T-SQL snippets can enforce complex business rules, ensuring data integrity and compliance. This article covers trigger syntax, multiple examples such as enforcing salary restrictions, and managing DELETE restrictions. Additionally, it explains INSTEAD OF triggers for non-updatable views, showcasing their unique functionality. Learn how triggers operate transparently and how to manage them effectively within SQL Server Management Studio.
Understanding Database Triggers: Syntax, Examples, and Business Rule Enforcement
E N D
Presentation Transcript
Triggers Event handlers in the DBMS
Triggers are event handlers • Triggers a executed when an event happens in the DBMS • Example events • INSERT, UPDATE and DELETE • Triggers contain [a little] code written in T-SQL • The code is executed before or after the INSERT, UPDATE or DELETE • Or sometimes INSTEAD OF the INSERT, UPDATE or DELETE! Triggers
Syntax • Create syntax CREATE TRIGGER someName ON someTableName | viewName FOR | AFTER | INSTEAD OF DELETE | INSERT | UPDATE AS T-SQL statements Triggers
Transparency • The database user does not know about triggers • There might be more than one trigger taking care of a single event on a certain table • It can be quite advanced and confusing to debug! Triggers
Triggers enforce business rules • If a constraint on a single table is not enough, trigger might be handy. • Triggers can do many thing, to many tables Triggers
Example: Name cannot change CREATE TRIGGER trStudentCannotChangeName ON student FOR UPDATE AS BEGIN IF UPDATE(name) BEGIN raiserror('Can not change student name', 16, 1); rollback tran; END END Triggers
Example: Teachers cannot be deletedTeachers will always be teachers CREATE TRIGGER trNoTeacherDelete ON teacher FOR DELETE AS BEGIN raiserror('You cannot delete a teacher', 16, 1); END; Triggers
Example:Teachers salary cannot go down CREATE TRIGGER trTeacherCannotLowerSalary ON teacher FOR UPDATE AS IF EXISTS ( SELECT 'true' FROM inserted JOIN deleted ON inserted. ID = deleted.ID WHERE deleted.salary > inserted.salary ) BEGIN RAISERROR('Cannot lower salary', 16, 1) ROLLBACK TRAN END • In an FOR UPDATE trigger you have access to the old (deleted) data and the new (inserted) data Triggers
Example: Cannot raise teachers salary more than 50 percent CREATE TRIGGER teacherSalaryChange ON teacher for UPDATE AS BEGIN IF EXISTS (SELECT 'true' FROM inserted JOIN deleted ON inserted.ID = deleted.ID WHERE (inserted.salary - deleted.salary)/deleted.salary > 0.5) BEGIN raiserror('Cannot raise salary more than 50 percent', 16, 1); rollback tran; END END Triggers
INSTEAD OF triggers • INSTEAD OF triggers is executed instead of the real action • Not before, or after • Useful to update views • Many views are not updateable • Example view CREATE VIEW vTeacherDepartment AS SELECT ID, Name, salary, departmentName FROM teacher INNER JOIN department ON teacher.departmentID = department.departmentID; • Example INSERT insert into vTeacherDepartment (teacherName, salary, departmentname) values ('Anders', 444, 'Computer Science'); • Result • Msg 4405, Level 16, State 1, Line 1 • View or function 'vTeacherDepartment' is not updatable because the modification affects multiple base tables. Triggers
INSTEAD OF triggers INSERT on a join view: Throwing exceptions CREATE TRIGGER trTeacherDepartmentInsert ON vTeacherDepartment INSTEAD OF INSERT AS BEGIN DECLARE @departmentID INT; SET @departmentID = (SELECT departmentID FROM department JOIN inserted ON department.departmentName = inserted.departmentName); IF (@departmentID IS NULL) BEGIN DECLARE @errormessagevarchar(50); SET @errormessage = 'No such department: ' + (SELECT departmentName FROM inserted); RAISERROR(@errormessage, 16, 1); ROLLBACK TRAN; END ELSE INSERT INTO teacher (name, salary, departmentID) SELECT name, salary, @departmentID FROM inserted; END Triggers
INSTEAD OF triggersINSERT on a join view: Double insert ALTER TRIGGER trTeacherDepartmentInsert ON vTeacherDepartment INSTEAD OF INSERT AS BEGIN DECLARE @departmentID INT; SET @departmentID = (SELECT departmentID FROM department JOIN inserted ON department.departmentName = inserted.departmentName); IF (@departmentID IS NULL) BEGIN DECLARE @deparmentname VARCHAR(100); INSERT INTO department (departmentName) SELECT departmentname FROM inserted SET @departmentID = @@IDENTITY; -- last identity value assigned in the current connection END INSERT INTO teacher (name, salary, departmentID) SELECT name, salary, @departmentID FROM inserted; END Triggers
Finding existing triggers using Microsoft SQL Server Management Studio Triggers
