Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Using INSERTED in a trigger?

    I'm mostly familar with SQL server, where I can use a special INSERTED table in a trigger. Does oracle have a similar mechanism?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Yes ... You specify in the heading of the trigger:

    CREATE OR REPLACE TRIGGER sysadm.trigger_name
    AFTER INSERT ON table_name REFERENCING NEW AS NEW OLD AS OLD
    ...
    ...

    variable := :new.column_name;

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    Make sure you leave out "OLD as OLD" on an INSERT trigger.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    within the body of the trigger you can use INSERTING, UPDATING and DELETING

    before insert or update of test
    for each row
    begin
    if inserting then
    ...
    end if;
    end;
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    hmmm... in SQL Server, the INSERTED table is a special object that holds whatever values are being inserted in the table in question

    example:

    i have a table with 2 columns, pricecut (int) and reason (varchar).
    if i insert the value 1 into the column price and 'discount' in the reason column, then i can read the INSERTED object as if it were a table (in this case, it would have 1 and 'discount' in their respetcive headers.

    is this possible in oracle?

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    using a for each row trigger with the ld and :new values, you can see it for a given record. however, oracle triggers do not allow one row to see another row.

    create trigger x
    before insert or update of test
    for each row
    begin
    if inserting then
    if :new.reason = 'discount' then
    ....
    elsif updating then
    :new.price := ld.price - :new.pricecut;
    ...
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    apparently a colon followed by a lower case O make this

    makes my message seem rather happy, doesn't it
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    you would have to create a temp_inserted table yourself
    OR
    Oracle Auditing can do this for you without a trigger.

    if you only want to track the old values for one table then
    maybe a trigger would be simpler. If you want to track all old values
    for the whole database then Auditing is the answer.
    (don't re-invent the wheel)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •