June 17, 2003 at 10:31 pm
Hey all,
I would like to shorten up the coding of the trigger below. The trigger is tracing the transaction done on table "smummd009" and insert it into table "smatth002".
create trigger InsUpDel_smummd009 on smummd009 for insert, update, delete
as
begin
if exists(select * from deleted)
begin
if exists(select * from inserted)
begin
if update(smnm)
begin
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval, oval)
select 'Update', user, 'smummd009', 'smnm', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), smnm,
(select smnm from deleted where deleted.mocd = inserted.mocd and deleted.smcd = inserted.smcd and deleted.lodt = inserted.lodt)
from inserted
end
if update(lofl)
begin
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval, oval)
select 'Update', user, 'smummd009', 'lofl', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), lofl,
(select lofl from deleted where deleted.mocd = inserted.mocd and deleted.smcd = inserted.smcd and deleted.lodt = inserted.lodt)
from inserted
end
end
else
begin
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, oval)
select 'Delete', user, 'smummd009', 'mocd', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), mocd
from deleted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, oval)
select 'Delete', user, 'smummd009', 'smcd', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), smcd
from deleted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, oval)
select 'Delete', user, 'smummd009', 'smnm', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), smnm
from deleted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, oval)
select 'Delete', user, 'smummd009', 'lofl', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), lofl
from deleted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, oval)
select 'Delete', user, 'smummd009', 'lodt', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), lodt
from deleted
end
end
else
begin
if update(mocd) or update(smcd) or update(lodt)
begin
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval)
select 'Insert', user, 'smummd009', 'mocd', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), mocd
from inserted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval)
select 'Insert', user, 'smummd009', 'smcd', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), smcd
from inserted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval)
select 'Insert', user, 'smummd009', 'smnm', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), smnm
from inserted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval)
select 'Insert', user, 'smummd009', 'lofl', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), lofl
from inserted
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval)
select 'Insert', user, 'smummd009', 'lodt', 'mocd,smcd,lodt',
rtrim(mocd) + ',' + rtrim(smcd) + ',' + rtrim(lodt), lodt
from inserted
end
end
end
Can someone please help. Any ideas would be appreciated.
Thank You.
Regards,
PlaYb_G
June 18, 2003 at 6:27 am
It looks like a fairly clean trigger already. What you could do to make it a little less repetitive or verbose is declare some variables for the keyn, tbln, rcid, nval, oval fields in your smatth002 table, just for readability, and you might change your subselect to an inner join?:
create trigger...
...
as
begin
declare
@tbln char(xx)
, @keyn char(xx)
-- set your repeating variables:
set @tbln = 'smummd009'
set @keyn = 'mocd,smcd,smnm'
...
if update(smnm) begin
insert into smatth002(txty, usid, tbln, fldn, keyn, rcid, nval, oval)
select 'Update', user, @tbln, 'smnm', @keyn, rtrim(inserted.mocd) + ',' + rtrim(inserted.smcd) + ',' + rtrim(inserted.lodt), inserted.smnm, deleted.smnm
from inserted
inner join deleted
on deleted.mocd = inserted.mocd
and deleted.smcd = inserted.smcd
and deleted.lodt = inserted.lodt
end
...
HTH,
jay
Edited by - jpipes on 06/18/2003 06:28:33 AM
June 18, 2003 at 11:47 pm
hey Jpipes,
Thanks a lot! 🙂
Is it possible to use the code below to get the same output? Because i don't know how to retrieve the "nval" value from table "smummd009". Do you have any idea about that?
create trigger InsUpDel_smummd009 on smummd009 for insert
as
begin
declare @scount int,
@fiel char(20),
@tbln char(20),
@keyn char(20)
set @scount = 1
set @tbln = 'smummd009'
set @keyn = 'mocd,smcd,smnm'
while @scount <> 6
begin
set @fiel = (select col_name(object_id('smummd009'),@scount))
insert into smatth002(txty, usid, tbln, fldn, keyn, nval)
select 'Insert',user, @tbln, @fiel, @keyn, ??
from inserted
set @scount= @scount + 1
continue
end
end
Thank You.
Regards,
PlaYb_G
June 19, 2003 at 6:35 am
I don't see a reason why not, however, I would suggest that the more verbose solution you had before might actually be a little more maintainable/readable, especially if your are working with a team of developers...
Good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply