CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
andreww
andreww

Reputation: 223

After insert Trigger

I'm trying to do a trigger which insert some into column after insert statement. For example I have table with column which looks:

Column1 Column2 Column3

And I'm inserting data into Column 1, Insert into Table(Column1) values ('256234','234234'). Now I would like automatically insert into COlumn2 TImestamp and into Column3 Value "Y", So output should looks:

Column1 Column2            Column3
256234  2015-10-28 08:48   Y
234234  2015-10-28 08:48   Y

Guys, could you help me with that? I tried to use cursor

Finally I got something like that:

create or replace trigger name
    after insert on table
declare
  c1 sys_refcursor;
  idx varchar2(200);
begin
open c1 for select Column1 from table ;
loop
       fetch c1 into idx;
       exit when c1%NOTFOUND;
       update table a1 set a1.Column2 = (select to_char(sysdate,'YYYYMMDDHHMISS') from dual) where Column1=idx;
       update table a1 set a1.Column3 = (select 'Y' from dual) where Column1=idx;
end loop;
close c1;
end;

It works fine, but I'm wondering if there is some other better solution than that?

Upvotes: 0

Views: 89

Answers (2)

Amol
Amol

Reputation: 428

You can create trigger as similar :

CREATE OR REPLACE TRIGGER  "TRG_NAME" 
  BEFORE INSERT ON "TABLE_NAME"               
  FOR EACH ROW  
DECLARE

BEGIN   

        :NEW.Column2 := to_char(sysdate,'YYYYMMDDHHMISS');
        :NEW.Column3 := 'Y';


END TRG_NAME ;
/
ALTER TRIGGER  "TRG_NAME"  ENABLE; 
/

Hope this PL/SQL will help you..

Upvotes: 1

user330315
user330315

Reputation:

No need for a cursor or even an update:

create or replace trigger name
    before insert on table_x
begin
   :new.column2 := sysdate;
   :new.column3 := 'Y';
end;
/

But you need a before trigger for this, because an after trigger cannot modify the newly inserted row.

But why don't you just define a default value for those columns, then you don't need a trigger at all:

create table table_x 
(
  column_1 integer, 
  column_2 date default sysdate,
  column_3 varchar(1) default 'Y'
);

Upvotes: 4

Related Questions