I have following sample trigger which uses SQL Server built in tables "deleted" and "inserted" tables. But Oracle we do not have equivalent. Oracle has only :OLD, :NEW, which represents only one row, but not table contains set of deleted/inserted rows.
could anyone please let me know what would be the equivalent trigger in Oracle?
Contributor
2350 Points
1014 Posts
Oracle equivalent for "deleted" "inserted" tables.
Apr 09, 2010 06:54 AM|duttavr|LINK
We are doing migration from SQL Server to Oracle.
I have following sample trigger which uses SQL Server built in tables "deleted" and "inserted" tables. But Oracle we do not have equivalent. Oracle has only :OLD, :NEW, which represents only one row, but not table contains set of deleted/inserted rows.
could anyone please let me know what would be the equivalent trigger in Oracle?
inserted tables deleted
Member
51 Points
16 Posts
Re: Oracle equivalent for "deleted" "inserted" tables.
Apr 09, 2010 10:19 AM|renafi|LINK
You don't have the inserted/deleted "tables".
If you want the same funcionality, you have to use the trigger with the FOR EACH ROW condition, and use the :OLD and :NEW, like you mentioned.
Contributor
6465 Points
5797 Posts
Re: Oracle equivalent for "deleted" "inserted" tables.
Apr 13, 2010 01:03 AM|wmec|LINK
you can refer to :old for having the original value of the field
see this
http://www.adp-gmbh.ch/ora/sql/trigger/new_old.html
Ai Guo
Contributor
2350 Points
1014 Posts
Re: Oracle equivalent for "deleted" "inserted" tables.
Apr 16, 2010 03:59 AM|duttavr|LINK
then it effects the performance in oracle.
lets say if we are deleting 10,000 records Oracle is firing the trigger 10K times. here :OLD or :NEW represents only one row.
where as in SQL Server triggered fired one time, we get all the deleted rows in deleted tables. This is pretty good.
is there no way to get all the deleted rows in Oracle withoutdoing some workaround manually?