1 / 13

Triggers

Triggers. Event handlers in the DBMS. 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

rane
Download Presentation

Triggers

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