Triggers in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 4
Division 5
Forward: In this part of the series, we look at the nature of triggers in Sybase SQL Anywhere.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
A trigger
Consider a trigger as an event handler. An event occurs, code is executed; that code is the event handler. When a trigger is executed we say the trigger is fired.
Trigger Execution Permissions
A trigger is associated with a table. A trigger is executed with the permission of the owner of the table and not the ordinary user who used the table and caused the trigger to fire. A trigger of one table can cause a trigger of another table to fire as well. In that case the owner of the first table need to have the permission to alter the second table. If the DBA owns all the tables that have the triggers, then there is no problem with ownership and firing triggers.
Trigger Events
A trigger is fired immediately an event occurs. A trigger is defined for one of the following events:
INSERT: Invokes the trigger whenever a new row is inserted into the table associated with the trigger.
DELETE: Invokes the trigger whenever a row associated with the table is deleted.
UPDATE: Invokes the trigger whenever a row associated with the table is updated.
Trigger Times
BEFORE trigger: A BEFORE trigger fires before a triggering action is performed; that is before the event of INSERT, DELETE or UPDATE is performed.
AFTER trigger An AFTER trigger fires after the triggering action is complete; that is after the event of INSERT, DELETE or UPDATE is performed.
Trigger Types
There are two types of triggers: row-level or statement-level.
A row-level trigger is one that executes once for each row that is changed. Row-level triggers execute BEFORE or AFTER the row is changed (INSERT, DELETE or UPDATE).
A statement-level trigger: See later.
The core code of a trigger is a compound statement.
When does a Trigger Fire?
A trigger is associated with a table, and it fires when an update, insert or delete event occurs. So a trigger is fired automatically as soon as any of these events occur. Remember that a trigger has to be written first by the programmer. If no trigger is written, nothing is fired.
The CREATE TRIGGER Statement
The CREATE TRIGGER Statement is used to create a trigger. Once you use this statement to create a trigger, the trigger is saved. You do not need any special command to save the trigger. The table name typed in the create trigger statement relates the trigger to the table. A simplified CREATE TRIGGER Statement is,
CREATE [ OR REPLACE ] TRIGGER trigger-name
trigger-type { trigger-event-list }
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
[ NEW AS new-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
trigger-body
column-list : column-name[, ...]
trigger-type :
BEFORE
| AFTER
trigger-event-list : trigger-event[, ... ]
trigger-event :
DELETE
| INSERT
| UPDATE
trigger-body : a BEGIN statement.
Remember, for SQL syntaxes, [] means optional and | means and/or, at that position.
You can start the statement with CREATE TRIGGER or CREATE OR REPLACE TRIGGER. When the OR REPLACE clause is used, it means if a trigger existed with the same name, it should be replaced.
trigger-name
trigger-name is the name of your choice you give to the trigger.
trigger-type
trigger-type is the keyword, BEFORE or AFTER and is to do with the timing.
trigger-event-list
This is either UPDATE or DELETE or INSERT or any two of them separated by comma, or all three of them separated by commas. It is advisable to use one, unless you have a good reason for using more.
ORDER Clause
You can define more than one trigger to fire for an UPDATE event. Also more than one can be defined to fire at the same time (same time means all BEFORE or all AFTER). When you have more than one trigger for the same event (e.g. several insert events) at the same time, you need to number the triggers with integers, so as to determine which one will fire first, which one will fire next, and so on. You give any numbers you want such as 1, 3, 8 or 15 for each trigger. This mean in one trigger you would have ORDER 1; in another you would have ORDER 3; in another ORDER 8, etc. The numbers do not have to be consecutive. The trigger with the smallest number is fired first; that with the number higher, is fired next, and so on.
table-name
table-name is the name of the table associated with the trigger.
You have the Insert row event and the delete row event. The Update process is equivalent to deleting a row and then inserting a new one. In the update process, you refer to the deleted row as old-name in the syntax; you refer to the inserted row as new-name. If you want to access any cell in the old-name row, the syntax is:
old-name.column-name
If you want to access any cell in the new-name row, the syntax is:
new-name.column-name
Note the use of the dot.
So far as the CREATE TRIGGER syntax is concerned, the INSERT event can only have a new-name row, since during Insert, a new row is inserted with no old row deleted. Also the Delete event can only have an old-name row, since during Delete, the old row is deleted and no new row is added.
old-name or new-name is a name of your choice you give for the row.
So, for Update, the REFERENCE line would be
REFERENCING OLD AS old-name
or
REFERENCING OLD AS new-name
For Insert, the REFERENCE line would be
REFERENCING OLD AS new-name
For Delete, the REFERENCE line would be
REFERENCING OLD AS old-name
In this series, the FOR EACH line is
FOR EACH ROW
It means the Update or Delete or Insert trigger is for each row.
WHEN Clause
If you remember, for the SELECT Query statement, the WHERE clause is used to choose which rows are selected. Now, for the CREATE TRIGGER statement, it is the WHEN clause that chooses which rows the trigger will respond on. If you omit the WHEN clause the trigger will respond on all (each of) the rows. The WHEN clause uses a search-condition just like the WHERE clause.
trigger-body
The trigger-body is a compound statement; it is the BEGIN statement. We have seen some examples of this before. We shall see another example in the next part of the series that is dealing with a trigger. All the normal SQL statements which form the really trigger action go into the BEGIN Statement.
Note: There is no semicolon after the instructions in the CREATE TRIGGER statement before the BEGIN statement.
Note: With triggers, after the Insert or Update or Delete has taken place, you can reverse the event. However, I will not address that in this series because, there are ways of knowing in advance if the Insert or Delete or especially, Update should really take place.
Dropping a Trigger
You can drop (erase) a trigger. You use the DROP TRIGGER statement for this. In simple terms, the syntax is:
DROP TRIGGER trigger-name;
Time to take a break. We stop here and continue in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT