1 / 13

Understanding Triggers in DBMS: Event Handlers for SQL Operations

Triggers in a Database Management System (DBMS) are automatic event handlers that execute custom T-SQL code in response to specific actions, such as INSERT, UPDATE, or DELETE. They are defined using the CREATE TRIGGER syntax and can enforce business rules when standard constraints are insufficient. Triggers can operate BEFORE, AFTER, or INSTEAD OF the actual database operation. They may interact with multiple tables and can be complex to debug, making it important for developers to understand their implementation and limitations, particularly with views.

rane
Télécharger la présentation

Understanding Triggers in DBMS: Event Handlers for SQL Operations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Triggers Event handlers in the DBMS

  2. Triggers are event handlers • Triggers are 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

  3. Syntax • Create syntax CREATE TRIGGER someName ON someTableName | viewName FOR | AFTER | INSTEAD OF DELETE | INSERT | UPDATE AS T-SQL statements Triggers

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. Finding existing triggers using Microsoft SQL Server Management Studio Triggers

More Related