Base Datos

Implementing Triggers

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

 Overview

 

Introduction to Triggers Defining Triggers




Examples of Triggers Performance Considerations

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

 Introduction to Triggers

 

What Is a Trigger Uses of Triggers



Considerations for Using Triggers

SistemasInformáticos – MTIE Javier Jesús Torres Yañez

What Is a Trigger

 

Associated with a Table Invoked Automatically




Cannot Be Called Directly Is a Transaction

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

Uses of Triggers



Cascade Changes Through Related Tables in a Database Enforce More Complex Data Integrity Than a CHECK Constraint






DefineCustom Error Messages
Compare Before and After States of Data Under Modification

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

Considerations for Using Triggers

 

Triggers Are Reactive; Constraints Are Proactive Constraints Are Checked First




Tables Can Have Multiple Triggers for Any Action Table Owners Must Have Permission to Perform All Trigger-defined StatementsTriggers Cannot Be Created on Views or Temporary Tables
Triggers Should Not Return Result Sets





Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

 Defining Triggers

 

Creating Triggers Altering and Dropping Triggers



How Triggers Work

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

Creating Triggers


CREATE TRIGGER Statement
 

RequiresAppropriate Permissions
Cannot Contain Certain Statements

CREATE TRIGGER member_delete ON member FOR DELETE AS IF EXISTS (SELECT d.member_no FROM loan l INNER JOIN deleted d ON l.member_no = d.member_no ) BEGIN RAISERROR ('Member has Books Checked Out *** Cannot Delete', 10, 1) ROLLBACK TRANSACTION END

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

Altering and DroppingTriggers


Altering a Trigger


Changes the definition without dropping the trigger



Can disable or enable a trigger

ALTER TRIGGER loan_insert ON loan FOR INSERT AS UPDATE c SET on_loan = 'y' FROM copy INNER JOIN inserted ON copy.isbn = inserted.isbn AND copy.copy_no = inserted.copy_no



Dropping a Trigger

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

 HowTriggers Work

 

How an INSERT Trigger Works How a DELETE Trigger Works


 

How an UPDATE Trigger Works How Nested Triggers Work
Recursive Triggers

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

How an INSERT Trigger Works

1

INSERT Statement to a Table with an INSERT Trigger Defined

2 3

INSERT Statement Logged Trigger Actions Executed

Sistemas Informáticos –MTIE Javier Jesús Torres Yañez

How an INSERT Trigger Works
INSERT statement to a table with an INSERT Trigger Defined
INSERT loan VALUES (603, 4, 11, 123, GETDATE(), (GETDATE() + 30))

loan
isbn copy_no title_no mem_no outdate duedate

1 4 603 4 3 3

1 1 2 4 2 1 1

1001 1004 11 1004 1002 1002

1001 1001 1002 123 1002 1003 1003

02/13/91 02/15/91 02/13/91 02/14/91 02/14/9102/14/91

02/27/91 03/17/91 02/27/91 02/28/91 02/28/91 02/28/91

Insert statement logged
inserted 603 4 11 123 02/15/91 03/17/91

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez

How an INSERT Trigger Works
TRIGGER Actions Execute
Trigger Code: USE library CREATE TRIGGER loan_insert ON loan FOR INSERT AS UPDATE c SET on_loan = 'Y' FROM copy c INNER JOIN inserted I ON c.isbn = i.sbnand c.copy_no = i.copy_no

loan
isbn copy_no title_no mem_no outdate duedate

1 4 603 4 3

1 1 4 2 1

copy 1001 1001 isbn 1004 copy_no 1001 11 123 1 1 1004 1002 4 1 603 4 1002 1003 4 2 3 1

02/13/91 02/27/91 title_no on_loan 02/15/91 03/17/91 02/13/91 02/27/91 1001 02/14/91 Y02/28/91 1004 11 02/14/91 Y02/28/91 1004 N 1002 N

Sistemas Informáticos – MTIE Javier Jesús Torres Yañez...