Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Row vs Statement Level Trigger 1
- Thread starter badamsmt
- Start date
-
1
- #2
sybaseguru
Instructor
In Sybase we do not have a row level trigger. This is more for Oracle where the trigger is executed for each row in the transaction.
In Sybase a trigger is always a statement level trigger. That is when the Sybase trigger is executed, it is executed at the transaction level. If your transaction affects one row or many rows, the trigger will only be executed once for the entire transaction.
I hope this explains the situation and good luck.
In Sybase a trigger is always a statement level trigger. That is when the Sybase trigger is executed, it is executed at the transaction level. If your transaction affects one row or many rows, the trigger will only be executed once for the entire transaction.
I hope this explains the situation and good luck.
- Thread starter
- #3
The ASA reference manual for 'CREATE TRIGGER' shows an option of ROW and STATEMENT.
Syntax
CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,.. ]
... [ ORDER integer ] ON table-name
... [ REFERENCING [ OLD AS old-name ]
[ NEW AS new-name ] ]
[ REMOTE AS remote-name ] ]
... [ FOR EACH { ROW | STATEMENT } ]
... [ WHEN ( search-condition ) ]
... compound-statement
You are saying that selecting ROW or STATEMENT will give the same results?
Syntax
CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,.. ]
... [ ORDER integer ] ON table-name
... [ REFERENCING [ OLD AS old-name ]
[ NEW AS new-name ] ]
[ REMOTE AS remote-name ] ]
... [ FOR EACH { ROW | STATEMENT } ]
... [ WHEN ( search-condition ) ]
... compound-statement
You are saying that selecting ROW or STATEMENT will give the same results?
sybaseguru
Instructor
Sorry what I said to you applies to "ASE". If ASA handles triggers like Oracle, then obviously the above argument needs to be modified to apply for each row or transaction. A transaction can affect one or more rows on the table and the transaction or statement level trigger is executed once only. A row level trigger is applied to each row affected.
Hope this helps
Hope this helps
- Thread starter
- #5
Ah...
For example, if I wrote a statement such as this and executed it:
Update Inventory
Set balance = 0
Where balance < 0
and it updated 10 records or rows, a ROW level trigger on the 'balance' field would execute 10 times but the STATEMENT level trigger on the 'balance' field would execute ONCE.
Now, is it too deep to ask how you could 'look' at the rows updated in a STATEMENT level trigger (for instance, you wanted to see the count of the number of rows updated?)
For example, if I wrote a statement such as this and executed it:
Update Inventory
Set balance = 0
Where balance < 0
and it updated 10 records or rows, a ROW level trigger on the 'balance' field would execute 10 times but the STATEMENT level trigger on the 'balance' field would execute ONCE.
Now, is it too deep to ask how you could 'look' at the rows updated in a STATEMENT level trigger (for instance, you wanted to see the count of the number of rows updated?)
sybaseguru
Instructor
OK let us explain Sybase statement level triggers
In Sybase ASE triggers are always executed after the triggering statement and are always considered statement-level triggers, i.e. they execute once at the end of each statement. In Sybase we have virtual tables called inserted (new) and deleted (old). These are actually created in the transaction log for trigger. These tables are always available and contain "all of the rows" afftected by the statement (i.e. transaction). So in your reference deleted will have all the old values and inserted will have the new values. If only one record is affected by your transaction then you can get the old and new values easily as described. For updates affecting multiple rows, within the trigger you can set up a cursor to fetch each rows affected by the transaction and perform the appropriate processing. Sybase transact SQL guide has good examples of update triggers. Have a look at them and come back if you need more help.
I hope this helps
In Sybase ASE triggers are always executed after the triggering statement and are always considered statement-level triggers, i.e. they execute once at the end of each statement. In Sybase we have virtual tables called inserted (new) and deleted (old). These are actually created in the transaction log for trigger. These tables are always available and contain "all of the rows" afftected by the statement (i.e. transaction). So in your reference deleted will have all the old values and inserted will have the new values. If only one record is affected by your transaction then you can get the old and new values easily as described. For updates affecting multiple rows, within the trigger you can set up a cursor to fetch each rows affected by the transaction and perform the appropriate processing. Sybase transact SQL guide has good examples of update triggers. Have a look at them and come back if you need more help.
I hope this helps
- Thread starter
- #7
nikhilparchure
Programmer
GURU
U ARE GREAT !!!!!!!!!!
rEGARDS
pARCHURE nIKHIL
U ARE GREAT !!!!!!!!!!
rEGARDS
pARCHURE nIKHIL
- Status
Similar threads
- Locked
- Question