I agree with Nick that using a trigger to update another table may not be a
good practice to follow all of the time, however, sometimes it is
necessary. Here is how I would write the trigger in a DB2/LUW environment.

create trigger trig1
after update of stn on stn
referencing old as o
new as n
for each row
mode db2sql
begin atomic
if o.stn_id <> n.stn_id
then update oper
set oper_stn_id = n.stn_id
where oper_stn_id = o.stn_id;
end if;
end;

With regards to automatically changing a date in a table on a daily basis
to the system date, you will need to schedule a daily job for this that
executes a single SQL statement to do the update.


Al Glinter, Lead Database Administrator
Federated Systems Group




"db2admin"
<db2udbdba-ezmlms
hield-x11561093.x To
[Email Address Removed] "LazyDBA Discussion"
A.com> <[Email address protected]
cc
03/13/2007 04:56
PM Subject
Re: Reg.Triggers










Sangeetha,

I'm not confident that writing a trigger on table A to update table B is a
good practice. If you do this too much you can easily find yourself in a
circular-triggering situation that you cannot get out of. I'd recommend
writing a stored procedure to handle the updates for both tables in the
same code body, and have the application program call it. Much cleaner
this way, no circular triggering action....

Nick

-------------- Original message --------------
From: "SangeethaPriya R. " <db2udbdba-ezmlmshield-x46704733.[Email address
protected]

> Hi friends,
>
> Please help me for triggers.
> Thats is when i insert or update on one table it should get updated in
> another table.
>
> I am using the following query. Its created without any errors. But not
> updating the fields.
>
> create trigger trig1 after update of stn on stn referencing old as o new
> as n for each row mode db2sql when (exists(select stn_id from stn where
> stn_id = o.stn_id))begin atomic update oper set oper_stn_id = n.stn_id
> where oper_stn_id = o.stn_id; end
>
>
> Likewise also, My date field in a table should automatically changed to
> system date daily by trigger.
>
> Please help me for these two.
>
> Thanks in Advance,
>
> Best Regards,
> Sangeetha Priya R.
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page