triggers n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Triggers PowerPoint Presentation
play fullscreen
1 / 13

Triggers

87 Views Download Presentation
Download Presentation

Triggers

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