CODEWITHSUNDEEP
sqldatabasetriggersdb2
Will Mcavoy
Will Mcavoy

Reputation: 495

Joining multiple table Sql trigger

Hi I am newbie to SQL trigger. since I tried and searched on online and I dont find any clear outcome. so here is my problem. I have three tables:

TABLE1 :

ID  NAME (columns )
1   prabhu

TABLE2 :

Id  COUNTRY (columns )
1   India

I want this to send to log table if anything like insert/update happen in table2 The SQL(DB2) trigger has to do the following and the result should be in log table like this

LOGTABLE:

ID  NAME     COUNTRY    
1   prabhu   India

Your help really appreciated.

Upvotes: 0

Views: 5445

Answers (2)

Will Mcavoy
Will Mcavoy

Reputation: 495

CREATE OR REPLACE TRIGGER logtableAfterUpdate
AFTER UPDATE ON table2 
REFERENCING NEW AS NAUDIT OLD AS OAUDIT 
FOR EACH ROW MODE DB2SQL 
--BEGIN --ATOMIC 
insert into logtable
values(
    (select id from table2 tbl2 where tbl2.id =OAUDIT.id),
    (select empName from table1 tbl1 where tbl1.id=(select id from table2 tbl2 where tbl2.id =OAUDIT.id)),
    (select country from table2 tbl2 where tbl2.id =OAUDIT.id)
);
--END;

Upvotes: 0

Raja S
Raja S

Reputation: 70

Try this,

-- Create tables
create table table1(id int, empName varchar(20));
create table table2(id int, country varchar(20));
create table logtable(id int, empName varchar(20), country varchar(20));

-- Create trigger
CREATE TRIGGER logtableAfterInsert ON table2
after INSERT,DELETE,UPDATE
AS
BEGIN
    declare @empid int;
    declare @empname2 varchar(20);
    declare @empcountry varchar(20);

    select @empid=i.id from inserted i;
    select @empcountry=i.country from inserted i;
    select @empname2=tbl1.empName from table1 tbl1 where tbl1.id=@empid;

    insert into logtable values(@empid,@empname2,@empcountry);

    PRINT 'Inserted'
END
GO

After that insert the values,

insert into table1 values(1, 'prabhu');
insert into table2 values (1, 'India');

Check the results,

select * from table1;
select * from table2;
select * from logtable;

Hope this resolves...

BTW, You need to add the foreign key constraint.

Upvotes: 1

Related Questions