我正试图阻止晚上7点到12点之间的插入操作。一切正常,只是触发器在将异常插入审计表之前触发了异常。我的问题是我如何能够同时阻止操作和记录尝试?
这是我试过的。
Attempt_Date DATE,
Operation VARCHAR2(10),
Table_Affected VARCHAR2(10));
Create or replace trigger audit_trigger
Before insert or update on rents
declare
Period_error EXCEPTION;
BEGIN
If TO_CHAR( sysdate, 'HH24MMSS' ) BETWEEN '060000' AND '100000' THEN
begin
RAISE Period_error;
IF INSERTING THEN
INSERT INTO Rent_Audit VALUES (SYSDATE, 'Insert', 'Rents');
End if;
IF UPDATING THEN
INSERT INTO Rent_Audit VALUES (SYSDATE, 'Update', 'Rents');
End if;
EXCEPTION
When Period_error THEN
RAISE_APPLICATION_ERROR (-20001, 'insertion not allowed on this time');
end;
end if;
END;```
如果您想要记录它,那么
- 触发器必须是自治事务(否则
RAISE
也会阻止插入到rent_audit
)- 这也意味着它必须
commit
- 这也意味着它必须
- 在
rent_audit
中插入一行后引发异常
像这样:
CREATE OR REPLACE TRIGGER audit_trigger
BEFORE INSERT OR UPDATE
ON rents
DECLARE
period_error EXCEPTION;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF TO_CHAR (SYSDATE, 'HH24MMSS') BETWEEN '060000' AND '100000'
THEN
BEGIN
IF INSERTING
THEN
INSERT INTO rent_audit
VALUES (SYSDATE, 'Insert', 'Rents');
END IF;
IF UPDATING
THEN
INSERT INTO rent_audit
VALUES (SYSDATE, 'Update', 'Rents');
END IF;
COMMIT;
RAISE period_error;
EXCEPTION
WHEN period_error
THEN
raise_application_error (-20001,
'insertion not allowed on this time');
END;
END IF;
END;