Scribd
Upload
79 views12 pages

Triggers

Triggers are SQL statements that are automatically executed by the database in response to data changes caused by insert, update or delete operations. Triggers are defined per table and can be set to execute before or after the data change event, with up to six triggers allowed per table - one for each event type (insert, update, delete) and timing (before, after). Triggers access virtual tables to reference the old and new data and can be used to update or log data changes.

Uploaded by

mathias
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
79 views12 pages

Triggers

Triggers are SQL statements that are automatically executed by the database in response to data changes caused by insert, update or delete operations. Triggers are defined per table and can be set to execute before or after the data change event, with up to six triggers allowed per table - one for each event type (insert, update, delete) and timing (before, after). Triggers access virtual tables to reference the old and new data and can be used to update or log data changes.

Uploaded by

mathias
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 12

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

You might also like