CODEWITHSUNDEEP
sqloracle-databasetriggers
Cogur
Cogur

Reputation: 65

How can i get all the deleted or updated lines in a trigger oracle

How can i get all the affected row in the trigger.

In sybase there is a table "deleted" which stores copies of the affected rows during delete and update statements

sybase documentation

Is there any solution in oracle which do the same in a table level trigger?

Upvotes: 2

Views: 1514

Answers (2)

DCookie
DCookie

Reputation: 43533

There is no analogue to the "deleted" table that I know of in Oracle.

Depending on what it is that you need to do, you can use a compound trigger to accomplish set based actions on the rows affected by the statement, as described at in Tim Hall's oracle-base site. Using this approach, you can define row-level triggers that accumulate (for example) a value or count, store it in the global part of the trigger, and access those values in the AFTER INSERT|UPDATE|DELETE sections in the trigger. The globals remain defined for the entire duration of all of the trigger points.

When triggers get this complex, however, I'm less of a fan of trigger-based solutions than putting the code in application packages/procedures/functions.

Upvotes: 1

Robert
Robert

Reputation: 25753

You can use new and old values in the trigger:

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE DELETE OR UPDATE ON table_name
  FOR EACH ROW
BEGIN    
    dbms_output.put('Old: ' || :old.column);
    dbms_output.put('New: ' || :new.column);
END;
/

It's not the same like in sybase, but it is what are you looking for.

Here you can find more information.

Upvotes: 1

Related Questions