TRIGGERS
TRIGGERS
• A group of SQL statements that are automatically executed by the database engine in
response to an insert, update or delete operation.
CREATING TRIGGERS
• The unique trigger name
• The table to which the trigger is to be associated
• The action that the trigger should respond to (INSERT, UPDATE or DELETE)
• When the trigger should be executed (BEFORE or AFTER the action)
CONDITIONS
• Triggers are defined per time per event per table, and only one trigger per time per event per
table is allowed. As such, up to six triggers are supported per table (before and after each of
INSERT, UPDATE and DELETE)
• A single trigger cannot be associated with multiple events or multiple tables
• If you need a trigger to be executed for both INSERT and UPDATE operations, you'll need to
define two triggers
• Triggers cannot be updated or overwritten. To modify a trigger, it must be dropped and re-
created.
DROP TRIGGER nameOfTrigger;
INSERT TRIGGERS
• INSERT triggers are executed before or after an INSERT statement is executed
• Within INSERT Trigger code, you can refer to a virtual table named NEW to access the rows
being inserted
• In a BEFORE INSERT trigger, the values in NEW may also be updated (allowing you to change
values about to be inserted)
• For AUTO_INCREMENT columns, NEW will contain 0 BEFORE and the automatically
generated value AFTER
SYNTAX – INSERT TRIGGERS
• CREATE TRIGGER nameOfTrigger
• AFTER INSERT ON nameOfTable
• FOR EACH ROW
• BEGIN
– SQL statements;
• END;
• Substitute AFTER with BEFORE where necessary
DELETE TRIGGERS
• DELETE triggers are executed before or after a DELETE statement is executed
• Within DELETE trigger code, you can refer to a virtual table named OLD to access the rows
being deleted
• The values in OLD are read-only and cannot be changed
SYNTAX – DELETE TRIGGERS
• CREATE TRIGGER nameOfTrigger
• AFTER DELETE ON nameOfTable
• FOR EACH ROW
• BEGIN
– SQL statements;
• END;
• Substitute AFTER with BEFORE where necessary
UPDATE TRIGGERS
• UPDATE triggers are executed before or after an UPDATE statement is executed
• Within UPDATE trigger code, you can refer to a virtual table named NEW to access the
newly updated values and OLD to access the original values of the table
• In a BEFORE UPDATE trigger, the values in NEW may also be updated (allowing you to change
values about to be used in the UPDATE statement)
• The values in OLD are read-only and cannot be changed
SYNTAX – UPDATE TRIGGERS
• CREATE TRIGGER nameOfTrigger
• AFTER UPDATE ON nameOfTable
• FOR EACH ROW
• BEGIN
– SQL statements;
• END;
• Substitute AFTER with BEFORE where necessary
BANKING TASK
• Create the following tables:
• Accountholder(accountNo[PK], accountName, CurrentBalance)
• Deposit(depositID[PK], accountNo, depositDate, depositAmount)
• Insert these records into accountholder:
– AC001,Nambi Nusra,500000
– AC002,Janat Hafswa,100000
– AC003,Musa Ahmed,20000
• Create a trigger that updates the current balance in accountholder for every new Deposit record inserted
• NEW BALANCE = currentBalance + depositAmount
BANKING TASK…
• Create a new table for capturing Activity Logs
• Logs (LogId, activity, tableAffected, activityTime, Details)
• Write triggers to automatically capture all activities (insert, update, delete) on each table
