Warm tip: This article is reproduced from serverfault.com, please click

A trigger inserting a row (not new) after update

发布于 2020-11-28 11:27:11

I want to create a trigger that inserts a row with the date in the novoitable along with the bi_id of the ri_di with the concerned date. my update is to change the N(not paid) on state attribute to 'Y'. So i don't want to insert into 'tracking' any new values. they are values that already exists and not updated. but after the trigger actually inserts 3lines with different dates and ri_id. i only need it to insert one!

Create table register(
BI_ID NUMBER,
ISBN Varchar(20),
RI_ID Varchar(50),
monthNum NUMBER,
CONSTRAINT register_pk PRIMARY KEY (AB_ID),
CONSTRAINT fk_magazine FOREIGN KEY (ISBN) REFERENCES MAGAZINE(ISBN),
CONSTRAINT fk_novoi FOREIGN KEY (IN_ID) REFERENCES novoi(IN_ID)
);

Create table novoi(
RI_ID Varchar(50),
CIN Varchar(50),
DATEI Date,
STATE Varchar(50),
CONSTRAINT nvoi_pk PRIMARY KEY (RI_ID),
CONSTRAINT fk_client FOREIGN KEY (CIN) REFERENCES CLIENT(CIN) 
);
CREATE TABLE Journalisation (
rui_ID NUMBER,
Datenovoi DATE,
BI_ID NUMBER,
CONSTRAINT FK_register FOREIGN KEY (BI_ID) REFERENCES register (BI_ID)
);

---

create or replace trigger T2
 after update on novoi 
 enable 
 declare
  CURSOR up_upda IS
    SELECT distinct novoi.datei, invoice.state, register.bi_id
    from register, novoi
    where  novoi.ri_id = register.ri_id ;        
begin
 for in_upda in up_upda loop
        dbms_output.put_line('check');
            INSERT INTO register(nui_id, datenovoi, bi_id) VALUES 
            (nui_seq.nextval, in_upda.datei, in_upda.bi_id);     
    end loop;
end; 
 update novoi set state = 'y' where datei = '04-04-19';
Questioner
nobo
Viewed
0
Popeye 2020-11-28 19:53:16

As far as I understood your problem, You can use the following code where I have used the :NEW. You haven't referenced the updated record in your trigger that is why 3 or multiple records were inserted into the target table.

CREATE OR REPLACE TRIGGER T2 AFTER
    UPDATE ON INVOICE
BEGIN
    DBMS_OUTPUT.PUT_LINE('check');
    INSERT INTO JOURNALISATION (
        JOUR_ID,
        DATEINVOICE,
        AB_ID
    )
        SELECT JOUR_SEQ.NEXTVAL,
               :NEW.DATEI,
               ABONNEMENT.AB_ID
          FROM ABONNEMENT
         WHERE :NEW.IN_ID = ABONNEMENT.IN_ID;

END;
/

Please comment if my understanding is not right or code is not working as per expectation.