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';
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.
It WORKED hh! I added 'for each row' tho because you can't use it ' in table level triggers'!
So i just didn't understand the actual use of new. I thought it is used when you actually update the attributes. But you used it on date even if it didn't change. THank you
Perhaps a word of explanation. NEW is a pseudo row containing the value for all columns that will be written to the data base. For update it will contain the columns values as specified in the statement and the prior values for columns not specified. (If you have multiple before triggers any of them can change the value for any column and that value will show up in the next trigger to fire.) Likewise OLD is a pseudo row containing the prior values for the row. NEW contains null for all columns on deletes, and OLD contains null for all columns on insert.