CODEWITHSUNDEEP
sqloracle-databasesql-insertdatabase-trigger
Czachodym
Czachodym

Reputation: 245

How can I make trigger in Oracle SQL to change rows in another table

I need to write a trigger in my SQL code that changes values in table A(Asortyment) that is connected with table B(Historia_Zamowien) with relation Many-Many. To connect A and B I use table C(Zamowienia_Asortyment).

How it looks like in relational model

I need to get to Asortyment.Dostepnosc through Zamowienia_Asortyment after INSERT ON Historia_Zamowien and change values to 0. I wrote some code that doesnt work and i have no idea what is wrong. Would you help?

CREATE TRIGGER "Zmiana_Dostepnosci_Po_Zamowieniu"
AFTER INSERT ON "Historia_Zamowien"
FOR EACH ROW
BEGIN
    UPDATE "Asortyment"
    SET tab1."Dostepnosc" = 0
    FROM "Asortyment" tab1 JOIN "Zamowienia_Asortyment" tab2 ON tab1."ID_sprzetu" = tab2."ID_sprzetu"
         JOIN inserted tab3 ON tab2."Numer_zamowienia" = tab3."Numer_zamowienia"
    WHERE tab1."ID_sprzetu" = tab2."ID_sprzetu" AND tab2."Numer_zamowienia" = inserted."Numer_Zamowienia"
END;
/

After i run the code i get:

Error(1,5): PL/SQL: SQL Statement ignored
Error(3,5): PL/SQL: ORA-00933: SQL command not properly ended
Error(7): PLS-00103: Endountered symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array

Upvotes: 0

Views: 61

Answers (1)

GMB
GMB

Reputation: 222432

There are several issues with your SQL :

  • in Oracle you cannot use JOIN within an UPDATE ; I replaced it with a WHERE EXISTS correlated subquery
  • you have repeated conditions in JOINs and WHERE clause, I simplified that
  • to refer to the newly inserted row in Historia_Zamowien, use the :NEW keyword (you seem to use inserted)

Try :

CREATE TRIGGER "Zmiana_Dostepnosci_Po_Zamowieniu"
AFTER INSERT ON "Historia_Zamowien"
FOR EACH ROW
BEGIN
    UPDATE "Asortyment" tab1 SET tab1."Dostepnosc" = 0
    WHERE EXISTS (
        SELECT 1
         FROM "Zamowienia_Asortyment" tab2 
         WHERE tab2."ID_sprzetu" = tab1."ID_sprzetu"
         AND tab2."Numer_zamowienia" = NEW."Numer_Zamowienia"
    )
END
/

Upvotes: 1

Related Questions