PLSQL Triggers

1
PL/SQL Triggers

• CIS 410

2
Trigger USE

• Maintaining complex integrity constraints
• Auditing Information in a table by recording
  changes made and who made them
• Automatically signaling other programs that
  action need to take place when changes are made
  to a table

3
Trigger

• Similar to a procedure
• Does not accept parameters
• Procedure is called from another block
• Trigger is executed implicitly when the trigger
  event happens
• Events INSERT, UPDATE, DElETE

4
Triggers

• Timing Before or After
• Level
• Row Level fires once for each row affected
• Statement Level Once either before or after the
  statement

5
Triggers Restrictions

• No control Statements Commit, Rollback,
  Savepoint The triggering statement will do this.
• No Long or Long Raw variables
• Restrictions on which tables a trigger body may
  access

6
Triggers

• Create or Replace Trigger t_name
• Before After
• UPDATE INSERT DELETE
• ON table-reference
• FOR EACH ROW WHEN trigger-condition
• Trigger-body

7
Triggers

• CREATE OR REPLACE TRIGGER CheckCredits
• BEFORE INSERT OR UPDATE OF
• current_credits ON Students
• FOR EACH ROW
• WHEN (new.current_credits gt 20)
• BEGIN
• / trigger body /
• END

8
Triggers

• CREATE OR REPLACE TRIGGER CheckCredits
• BEFORE INSERT OR UPDATE OF
• current_credits ON Students
• FOR EACH ROW
• BEGIN
• IF new.current_credits gt 20 THEN - - note
  colon and dot
• / trigger body /
• END IF
• END

9
Triggers Old and New

• New pseudo record for INSERT and UPDATE
• Old pseudo record for Delete and UPDATE
• Only place in PL/SQL where is used
• new.flight_no flight_no must be a field in
  the triggering table
• Valid only in row level triggers

10
Triggers

• Alter Trigger trigger_name disable
• Alter Trigger trigger_name enable
• Alter table flight disable all triggers
• Drop trigger trigger_name