Oracle触发器的简单例子

--行级触发器 增、删、改

CREATE OR REPLACE TRIGGER TRI_SSO_USERS
  AFTER INSERT OR UPDATE OR DELETE ON USERS
  FOR EACH ROW
DECLARE
  TEMP_ROW_COUNT INT;
BEGIN
  IF (UPDATING OR INSERTING) THEN
    
    SELECT COUNT(*)
      INTO TEMP_ROW_COUNT
      FROM SSO_USER_INFO SSO
     WHERE SSO.SSO_USER_ID = :NEW.USER_ID;
  
    IF (TEMP_ROW_COUNT > 0) THEN
      UPDATE SSO_USER_INFO SSO
         SET SSO.SSO_PASSWORD = :NEW.PASSWORD
       WHERE SSO.SSO_USER_ID = :NEW.USER_ID;
    ELSE
      INSERT INTO SSO_USER_INFO
        (SSO_USER_ID, SSO_PASSWORD, APP_CODE, APP_USER_ID)
      VALUES
        (:NEW.USER_ID, :NEW.PASSWORD, 1001, :NEW.USER_ID);
    END IF;
  
  ELSIF (DELETING) THEN
    DELETE FROM SSO_USER_INFO SSO WHERE SSO.SSO_USER_ID=:OLD.USER_ID;
  END IF;
END;