CODEWITHSUNDEEP
oracle-databasetriggers
Eric
Eric

Reputation: 373

Creating a trigger on Oracle 11g

I need to create a trigger that updates another table whenever the trigger table has an insert into command, or an after insert trigger.

I need to pull the id that's being inserted into the table in order to update the other table, how do I go about doing so?

In case that's confusing, another attempt at my question:

Table 1 has an after insert trigger. Said trigger updates table 2 based on one of the id values being inserted into table 1. How do I pull said id value from table 1 in the trigger?

Upvotes: 0

Views: 730

Answers (2)

anudeepks
anudeepks

Reputation: 1132

 CREATE OR REPLACE TRIGGER testempdel
    AFTER insert ON testemp
     FOR EACH ROW
    BEGIN
        update  testdelvalues set salary=:New.sal;
    END;
    /

Trigger created.

Initially the salary is null

SQL> select * from testdelvalues;

     EMPNO EMPNAME        SALARY
---------- ---------- ----------
      7369 SMITH

After inserting the value in the other table is updated

SQL> insert into testemp (empno,ename,sal) values (1231,'TESTUSER',1000);

1 row created.

SQL> select * from testdelvalues;

     EMPNO EMPNAME        SALARY
---------- ---------- ----------
      7369 SMITH            1000

Upvotes: 0

zgguy
zgguy

Reputation: 226

You can use :new in your trigger to reference the values being inserted, for example

create or replace trigger <trigger_name>
after insert on <table_name>
for each row
declare
    l_id number;
begin
    select :new.id into l_id from dual;
    -- now l_id contains the id of the inserted row, do what you want with it
end;

Don't take the example to literally; you don't have to first select :new.id into a variable, you can use it directly in SQL inside the trigger. I did it here just for illustration.

Take a look at the Oracle docs: Coding Triggers

However, you might also want to take a look at some arguments why you should think twice if you really need to put your logic into triggers: The Trouble with Triggers

Upvotes: 2

Related Questions