Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Update trigger to update a time column fails..

    Hi,

    I ve a test table defined with the following columns,

    Code:
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER(11)
     TIME                                      NOT NULL DATE
    Here, whenever any of the table column is updated, the 'TIME' column should be updated with the date and time of update.

    i wrote the following triggers, both compiled successfully but failed to execute..

    Code:
    CREATE OR REPLACE TRIGGER TRIG_UPDATETEST1 AFTER UPDATE ON test
    FOR EACH ROW
    
                    UPDATE test SET
                            id = :NEW.id,
                            time = (SYSDATE)
                    WHERE id = :NEW.id;
            END;
    /

    Code:
    CREATE OR REPLACE TRIGGER TRIG_1 AFTER UPDATE ON test
    FOR EACH ROW 
    
    DECLARE 
    COLROWID 	 ROWID;
    COL1 	 test.id%TYPE;
    COL2 	 test.time%TYPE;
    
    CURSOR CURSOR1 IS 
    SELECT 
    	A.ROWID,
    	:NEW.id,
    	(SYSDATE)
    FROM test A
    WHERE A.id = :NEW.id;
    
    BEGIN 
    
    	OPEN CURSOR1;
    	LOOP
    	FETCH CURSOR1 INTO COLROWID, COL1, COL2;
    	EXIT WHEN CURSOR1%NOTFOUND;
    	BEGIN
    		UPDATE updatetest SET
    			id = COL1,
    			time = COL2
    		WHERE ROWID = COLROWID;
    	END;
    	END LOOP;
    	CLOSE CURSOR1;
    END;
     /
    Kindly let me know, whats wrong with the above triggers or is there any simple triggers available for me...

    Thanks,
    Sn

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here you are:
    Code:
    CREATE OR REPLACE TRIGGER trig_upd_test
      BEFORE UPDATE ON TEST
      FOR EACH ROW
    BEGIN
      :NEW.TIME := SYSDATE;
    END;
    /

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Hi,

    Thanks for the code, that works fine.


    Sn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •