Oracle trigger if updating then
Many applications update all of the columns in a table -- setting them to their old value, rather then trying to figure out exactly which columns have been updated by the client. create or replace trigger test_trigger 2 after update of x,y on test 3 for each row 4 begin 5 if updating( 'X' ) then 6 dbms_output.put_line( 'X is being updated' ); 7 end if; 8 9 if updating( 'Y' ) then 10 dbms_output.put_line( 'Y is being updated' ); 11 end if; 12 end; 13 / Trigger created.SQL Notice in the last update it indicated that x and y both were being updated (which they were), but the update didn't change the value (both x and y were 1 prior to the update which set them to 1). You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed.This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. By joining today you can post your own programming questions, respond to other developers questions, and eliminate the ads that are displayed to guests. Hi I am currently using Oracle 9i, using the Scott database and have a number of contraints which I need to implement.I have very little knowledge of creating triggers, but have had a go. 2 , TEST_TABLE_1(number, balance) TEST_TABLE_2(number, datetime, value, different). ACRIONS_COMMIT_TR after update on quickstart.actions for each... Insert, update - 3 borland builder: , , insert, update...... The actions that I need to do on my -- Scenario 1 CREATE OR REPLACE TRIGGER my_trg BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW BEGIN IF UPDATING THEN IF : NEW.my_col IS NULL THEN raise_application_error(...); END IF; IF : NEW.my_col2 IS NULL THEN : NEW.my_col2 := (...); END IF; END IF; IF INSERTING THEN IF : NEW.my_col3 IS NULL THEN SELECT my_seq.NEXTVAL INTO : NEW.my_col3 FROM DUAL; END IF; IF : NEW.my_col4 IS NULL THEN (...) END IF; END IF; END; / -- Scenario 2 CREATE OR REPLACE TRIGGER my_trg1 BEFORE INSERT ON my_table FOR EACH ROW BEGIN IF : NEW.my_col3 IS NULL THEN SELECT my_seq.
CREATE OR REPLACE TRIGGER check_dept BEFORE INSERT OR UPDATE OF loc ON dept FOR EACH ROW WHEN(HOUSTON) DECLARE v_OILdept.loc%TYPE; BEGIN SELECT loc, INTO v_loc FROM dept WHERE loc = :'HOUSTON'; IF :deptname = 'OIL' RAISE_APPLICATION_ERROR(-20102Update succeeded); END IF; END; / Q4) The itemtot attribute (the inclusion of which in the database is evidence of very bad design) needs to be automatically maintained and not be directly updateable.
However I would be grateful if someone could help me with the syntax, code, layout etc.