---==建立DML触发器
-----建立BEFORE语句触发器
CREATE OR REPLACE TRIGGER TR_SEC_EMP
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
IF TO_CHAR(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR(-20001, '不能在休息日改变雇员信息');
END IF;
END;
---谓词 inserting,updating,deleting的用法
CREATE OR REPLACE TRIGGER TR_SEC_MEP
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
IF TO_CHAR(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN
CASE
WHEN INSERTING THEN
RAISE_APPLICATION_ERROR(-20001, '不能在休息日增加雇员');
WHEN UPDATING THEN
RAISE_APPLICATION_ERROR(-20002, '不能在休息日更新雇员');
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, '不能在休息日删除雇员');
END CASE;
END IF;
END;
---创建after语句触发器
CREATE TABLE audit_table(
NAME VARCHAR2(20),
ins INT ,
upd INT,
del INT,
starttime DATE,
endtime DATE);
CREATE OR REPLACE TRIGGER TR_AUDIT_EMP AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
V_TEMP INT;
BEGIN
SELECT COUNT(*) INTO V_TEMP FROM AUDIT_TABLE WHERE NAME = 'emp';
IF V_TEMP = 0 THEN
INSERT INTO AUDIT_TABLE VALUES ('EMP', 0, 0, 0, SYSDATE, NULL);
END IF;
CASE
WHEN INSERTING THEN
UPDATE AUDIT_TABLE
SET INS = INS + 1, ENDTIME = SYSDATE
WHERE NAME = 'EMP';
WHEN UPDATING THEN
UPDATE AUDIT_TABLE
SET UPD = UPD + 1, ENDTIME = SYSDATE
WHERE NAME = 'EMP';
WHEN DELETING THEN
UPDATE AUDIT_TABLE
SET DEL = DEL + 1, ENDTIME = SYSDATE
WHERE NAME = 'EMP';
END CASE;
