here are the commands to create the SYSTOOLS TRIGGERS - the trigger
statements are always present in the column of text of SYSCAT.TRIGGERS
View of any other database where the triggers are not dropped.
CREATE TRIGGER SYSTOOLS.POLICY_DR NO CASCADE BEFORE DELETE ON
SYSTOOLS.POLICY
REFERENCING OLD AS OLD_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE OLD_ROW.MED = MED AND
OLD_ROW.DECISION = DECISION ) = 1 ) THEN SIGNAL SQLSTATE '85101' ('At
least one policy is required.'); END IF; END;
CREATE TRIGGER SYSTOOLS.POLICY_IR NO CASCADE BEFORE INSERT ON
SYSTOOLS.POLICY
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE NEW_ROW.MED = MED AND
NEW_ROW.DECISION = DECISION ) = 1 ) THEN SIGNAL SQLSTATE '85101' ('Only
one policy is allowed.'); END IF; END;
CREATE TRIGGER SYSTOOLS.POLICY_IV NO CASCADE BEFORE INSERT ON
SYSTOOLS.POLICY
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY)); END;
CREATE TRIGGER SYSTOOLS.POLICY_UV NO CASCADE BEFORE UPDATE ON
SYSTOOLS.POLICY
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY)); END;
Post by sreeHi,
We can see some system defined triggers in DB2 which belongs to SYSTOOLS schema
(DB2 ControlCenter -> database_name -> Triggers)
What I need to know is --- how do we restore any of these triggers , If we accidentaly delete any of them.
Thanks
sree