Need to insert single record into the audit table after trigger is called-CodePudding

CREATE TABLE trg_demo 
(
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    CONSTRAINT pk_trg_demo PRIMARY KEY (e_id)
);

CREATE SEQUENCE trg_audit_sq;

CREATE TABLE trg_audit 
(
    a_id           NUMBER(10),
    modified_date  TIMESTAMP,

    CONSTRAINT pk_trg_audit PRIMARY KEY (a_id)
);

Trigger:

CREATE OR REPLACE TRIGGER trigger_trg
BEFORE INSERT
ON trg_demo
FOR EACH ROW
BEGIN
    INSERT INTO trg_audit 
    VALUES (trg_audit_sq.nextval, current_timestamp);
END;

INSERT INTO trg_demo VALUES (1, 'A');
INSERT INTO trg_demo VALUES (2, 'B');

Expected output:

 ------ --------------- 
| A_ID | MODIFIED_DATE |
 ------ --------------- 
|    1 | 16-12-21      |
 ------ ---------------

I am going to insert rows into the trg_demo table once in 2 days. So, suppose today if I am inserting 2 rows then in trg_audit table, it will insert only one row with today's date and then after 2 days if I insert 4 rows into the trg_demo table then in trg_audit it will have a second row with A_ID as 2 and date as 18-12-21.

CodePudding user response：

You can use condition inside trigger to prevent the rest and accepts only one record :

CREATE OR REPLACE TRIGGER trigger_trg
BEFORE INSERT
ON trg_demo
FOR EACH ROW
  declare 
flag number;
BEGIN

select count(*) into flag from trg_audit d where trunc(d.modified_date) = trunc(sysdate) ;

if flag < 1 then 
    INSERT INTO trg_audit
    VALUES (trg_audit_sq.nextval, current_timestamp);
    end if ;
END;