lundi 31 octobre 2016

TRIGGER UPDATE WITH IF CONDITION

create or replace
TRIGGER TR_SITECONTACT_UPDATE
AFTER  UPDATE OR INSERT  ON s_ct 
FOR EACH ROW
DECLARE
v_SID s_ct.sid%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN   
IF :NEW.CTID != :OLD.CTID THEN
      UPDATE CT
      SET lastupdatedon =sysdate,
        LASTUPDATESITE=:NEW.SID
      WHERE CTID  = :NEW.CTID;
COMMIT;
END IF;
END;

Here it's possible to check whether lastupdatedCOF is null or not then use update statement, before update row i need to check lastupdatedCOF IS NULL OR NOT in CT Table. IF Null means i need to use below update statement

UPDATE CT SET lastupdatedon =sysdate, LASTUPDATESITE=:NEW.SID WHERE CTID = :NEW.CTID; COMMIT;

lastupdatedCOF IS NOT NULL Means

UPDATE CT SET lastupdatedon =sysdate, LASTUPDATESITE=:NEW.SID, lastupdatedCOF = NULL WHERE CTID = :NEW.CTID; COMMIT;

Aucun commentaire:

Enregistrer un commentaire