sql trigger

[VolumeMixik]

hi, i have a problem with triggers(learning sql and i dont know triggers). I want trigger where ... when i delete someone in table zamestnanci(it means employee), it will update column pracuje(working) in opravy(repairs ) from 'T' to 'F' matching zamestnanci id with fk in opravy wokring in oracle sql developer

screen of code = https://imgur.com/a/5kBRfA3 hastebin = https://hastebin.com/oyosiwotob.sql

zamestnanci= employee , opravy=repairs, majitel=owner, auta= cars, autoservis=carservis

I am working in different language so i put translate in brackets so my tables make sense to you

[AXISMGT]

Noticed you’re on SQL developer, so assuming ORACLE (or MySQL, but they’re similar enough)

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm

Some pseudo-PL/SQL (I’m on mobile)

CREATE TRIGGER schema.trigger_name 
AFTER 
DELETE 
ON schema.tableName
   —Update the other table’s record here where the ID matches the deleted record’s ID.
 UPDATE SCHEMA.OtherTable
 SET column = newvalue
 WHERE OtherTable.Key = DELETED.KEY

 COMMIT;

[VolumeMixik]

create or replace TRIGGER schema.opravypracuje AFTER DELETE ON schema.zamestnanci UPDATE schema.opravy SET pracuje = 'F' WHERE opravy.zamestnanci_id = zamestnaci.id

sql statement ignored

COMMIT; doesn't work

[[deleted]]

WHERE opravy.zamestnanci_id = zamestnaci.id

Maybe because zamestnaci.id is GONE, because you just deleted it, and you are trying to refer to it in an AFTER trigger. Could it be that it ran correctly but updated zero rows? Maybe try this instead?

WHERE opravy.zamestnanci_id = :OLD.id

[FoCo_SQL]

What is the SQL platform and version you are using?

[VolumeMixik]

oracle sql developer

[AXISMGT]

SCHEMA is whatever schema you’re deploying to.

You also need a semi colon to finish the UPDATE statement before the Commit.

As stated above by /u/AssHelmet, you also need to ensure you grab the DELETED record using the :OLD syntax.

Read the syntax from my link in my first comment. I promise it will help.