Categories
DATABASE

Oracle trigger sample

CREATE OR REPLACE TRIGGER SET_items_LSUPDATE
AFTER INSERT OR UPDATE
ON items_FILE REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
  OLDG_NO NUMBER(14);
BEGIN
  SELECT COUNT(*) INTO OLDG_NO FROM BIDLEADER.items_LASTUPDATE WHERE G_NO=:Old.G_NO;

  IF OLDG_NO >= 1 THEN
    UPDATE BIDLEADER.items_LASTUPDATE SET G_LASTUPDATE=SYSDATE WHERE G_NO=:Old.G_NO;
  ELSIF OLDG_NO=0 THEN
    INSERT INTO BIDLEADER.items_LASTUPDATE VALUES (:New.G_NO, SYSDATE);
  END IF;

END SET_items_LSUPDATE;