I need to write triggers for the following rules

TB_CLIENT – On Insert
Any inserted client record must be inserted into TB_SEARCH_CLIENT.

TB_CLIENT – On Update
Any client record updated where fields contained in TB_SEARCH_CLIENT are
modified, the corresponding record must be updated in TB_SEARCH_CLIENT.

I have developed the insert trigger(correct me if I a wrong)

CREATE TRIGGER INSERT_SEARCH_PROVIDER
AFTER INSERT
ON TB_PROVIDER
REFERENCING NEW AS NEW_VAL
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO TB_SEARCH_PROVIDER
VALUES
(NEW.PROVIDER_ID,etc
);
END

About the update trigger, I have currently a trigger which updates within
the table,how do I update another table if there is a change in the base
table.

CREATE TRIGGER TR_provider_upd
NO CASCADE AFTER UPDATE OF
PROVIDER_ID,
PROVIDER_PREFIX_CD,
PROVIDER_FIRST_NM
ON TB_PROVIDER
REFERENCING OLD AS OLD_VAL
NEW AS NEW_VAL
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

What do I need to include here after atomic.

Would a table replication be a easy way to do this instead of writing
triggers, we shall be updating and inserting about 1000 rows.

Thanks
Anirudh



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