阻塞插入操作，并记录在oracle的另一个审计表上

Question

我正试图阻止晚上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;```

Answer 1

如果您想要记录它，那么

• 触发器必须是自治事务(否则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;