8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Database Triggers Overview


poelger said...

Maybe you should warn in your article about using non-transactional operations(package variables, autonomous transactions, ...) in triggers.
Oracle can restart DML statements.
I described this at our blog:
https://iadviseblog.wordpress.com/2010/12/17/statement-restart/

And it is discussed on the Pl/SQL Challenge blog:
http://plsql-challenge.blogspot.com/2011/02/dml-restarts-only-happen-with.html

Tim... said...

Hi.

Good point. I've added it into the the "Should you use triggers at all?" section.

Cheers

Tim...

lesio said...

Very good article. DML restarts are kind of shock :p

Jeroen said...

Hi,

About the behaviour in merge statements:

ELSE
-- Include any code specific for when the trigger is fired from a MERGE.
-- A merge is not recognized as either of the three conditions, so it is implied.

It seems this was more or less a bug prior to 11g2. From this version on a generic statement trigger fires twice, oce with UPDATING=TRUE and once with INSERTING=TRUE

Tim... said...

Hi.

Thanks for the heads-up. I've corrected the article and included the way the fact the delete is not triggered for a delete clause of a merge. :)

Cheers

Tim...

Darragh said...

Hi,

a typo in the DDL for creating triggers focused on updates for specific column

instead of:
UPDATE FOR column-name[, column-name ...]

it should read:
UPDATE OF column-name

Darragh

Tim... said...

Hi.

It's not a typo. UPDATE OF can reference multiple columns. Check out the syntax diagram.

https://docs.oracle.com/database/121/LNPLS/create_trigger.htm#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B__BABGDFBI

"column-name[, column-name ...]" means one or more columns comma separated, which is correct.

Cheers

Tim...

Darragh said...

Hi Tim,

just to clarify I was not focused on single or multiple columns instead I was focused on the invalid syntax
i.e.
instead of:
UPDATE FOR column-name[, column-name ...]

it should read:
UPDATE OF column-name[,column_name...]

Example:
create or replace trigger triggerName
after insert or update of columnOne,columnTwo on tableName

Darragh.

Tim... said...

LOL. Oh. My eyes focused on the column list bit. :) Yes. I've corrected they typo. :)

Cheers

Tim...

Darragh said...

Super - :)

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.