Table Level Triggers – Triggers on Two Fields Within the Same Table

30 pts.
Tags:
Development
Geographic information systems applications
Oracle
Hi, I was wondering if some can have a look at the following triggers on a single table but on two different Fields that are referenced in both triggers: CREATE OR REPLACE TRIGGER AAL015_HWT_TRG BEFORE INSERT OR UPDATE ON AAL015 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF INSERTING OR UPDATING THEN IF:NEW.HWT IN (0,2,3,4,5,6,7,8,9,11,14,15,16,20,21,999) THEN :NEW.RFC:=1; END IF; END IF; END; / CREATE OR REPLACE TRIGGER AAL015_RFC_TRG BEFORE INSERT OR UPDATE ON AAL015 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF INSERTING OR UPDATING THEN IF:NEW.RFC IN (0,2,4,999) THEN :NEW.HWT:=998; END IF; END IF; END; / The problem is that one will work, but the other doesn't. I alomost appears that there is a conflict. It appears that the only trigger that works it the AAL015_HWT_TRG trigger. Any time when I set the RFC with one of the values (0,2,4,999) the changes that should be triggered off by the AAL015_RFC_TRG trigger are not implemented, which should set the HWT back to 998. What I'm trying to do is impose some Schema rules for collecting GIS information. Any suggestions as to what could be causing one not to fire off would be much appreciated.

Answer Wiki

Thanks. We'll let you know when a new response is added.

It may sound obvious, but have you verified that the trigger has been added to the file and if so that it has not been disabled. (re: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/dbp/rbafotrgncmd.htm)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kurthp
    Hi Sam. I created a test table with the 2 columns and triggers and they do appear to both work. Problem is that as soon as RFC is IN (0,2,4,999), and you then try to change the HWT from 998 to something else, the AAL015_RFC_TRG fires and sets HWT back to 998. Try changing line 11 in the AAL015_HWT_TRG to: IF:NEW.HWT :OLD.HWT AND :NEW.HWT IN (0,2,3,4,5,6,7,8,9,11,14,15,16,20,21,999) Try changing line 10 in the AAL015_RFC_TRG to: IF:NEW.RFC :OLD.RFC AND :NEW.RFC IN (0,2,4,999)
    195 pointsBadges:
    report
  • SamAvella
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/table-level-triggers-triggers-on-two-fields-with... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following