SQL Trigger Delete Row if Value is 0

Question

I am trying to create a trigger that checks if the value after an update is 0 for a specific column. If the value is 0, the row should be deleted. I am not really familiar with triggers. I would apprecitate it if someone clould show/explain it to me. This is what I tried:

CREATE TRIGGER [dbo].[after_update] 
   ON [dbo].[tbl_LagerPos]
   AFTER UPDATE
AS 
BEGIN
    DELETE FROM tbl_LagerPos WHERE Bestand = 0;
END
GO

Here Bestand is the one that should be checked if 0. If yes, then the whole row should be deleted. I know that this is checking the entire table and is basically wrong. So I need it for that specific record only.

Does this have to happen immediately after every edit, or can you just run that DELETE query periodically to remove the unwanted records? — Robert Harvey, Feb 05 '19 at 03:12
I mean it should run after an update was made to the table, then check if it is 0 and then delete it based on wheter it is 0 or not. I would need to check it for the insert as well but I didn't want to ask for too much. — Tom el Safadi, Feb 05 '19 at 03:13
Yes, I knew what you meant. Does it have to happen on every update, or can you just run it once or twice per day? — Robert Harvey, Feb 05 '19 at 03:14
`However, it doesn't work anyway.` ... well what exactly _does_ happen when you do an update? — Tim Biegeleisen, Feb 05 '19 at 03:14
Sorry, it actually does work. I just need it for the specific record that is updated. @TimBiegeleisen — Tom el Safadi, Feb 05 '19 at 03:17
You may want `DELETE FROM tbl_LagerPos WHERE Bestand = 0 and id in (select id from inserted);` And you can change `AFTER UPDATE` to `AFTER INSERT, UPDATE` to get it on insert as well. — Dale K, Feb 05 '19 at 03:23
@DaleBurrell I agree completely and that looks like an answer to me. — Tim Biegeleisen, Feb 05 '19 at 03:24
Perfect! If you post it as the answer I can mark it. @DaleBurrell — Tom el Safadi, Feb 05 '19 at 03:38
Thanks @TomelSafadi but actually that question has been asked many times before - so I'm flagging it as a duplicate. — Dale K, Feb 05 '19 at 03:41

score 0 · Answer 1 · answered Feb 05 '19 at 06:49

0

Do not use a trigger for this. Make a check on the client. You can also add a column constraint to the database:

alter table dbo.Table1 add Column1 int not null constraint CHK_Column1 check (Column1 > 0);

answered Feb 05 '19 at 06:49

Alexey Chuksin

90
4

SQL Trigger Delete Row if Value is 0

1 Answers1