Re: Triggers in SQL Server - triggers in Oracle

From: Marin Dimitrov <marin_at_sirma.bg>
Date: Fri, 6 Oct 2000 22:02:40 +0300
Message-Id: <10641.118710@fatcity.com>

• Original Message ----- From: Helmut Daiminger <hdaiminger_at_vivonet.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, October 06, 2000 02:00 Subject: Triggers in SQL Server - triggers in Oracle

>
> b) how can I modify the values of variables inside a trigger?
>
> the equivalent in Oracle would be:
>
> IF :NEW.STATUS='A0' THEN
> :NEW.STATUS:='AC';
>

As far as I remember there are logical tables INSERTED and DELETED in MS SQL, the first contains rows that are inserted and updated, the second contains rows that are deleted and updated. By joining the INSERTED and DELETED tables with the related table u can get the new/old values of the rows. Note that u cannot modify INSERTED and DELETED tables.

This sample seems to work, of course u should check the docs for more complex tasks

CREATE TRIGGER test_trigger

    ON marin_test
    FOR INSERT AS
BEGIN

     UPDATE marin_test
     SET    marin_test.test_char = 'AC'
     FROM  inserted
     WHERE  marin_test.test_id = inserted.test_id
             AND   inserted.test_char = 'A0'

END hth,

    Marin

---

"The happier people can be, the unhappier they are..."

                              "Veronika decides to die", Paolo Coelho

Received on Fri Oct 06 2000 - 14:02:40 CDT