CODEWITHSUNDEEP
sqlsql-serverdatabasetriggerssql-update
Elizabeth
Elizabeth

Reputation: 765

SQL Delete Trigger not working

I have the following trigger 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TG_LT_box_name_delete] 
ON [dbo].[lt_box_naming]
AFTER DELETE
AS
Set NoCount On 

update a
set used_customer_no = NULL
-- select a.* , '' as '||||', b.*
from lt_facility_ref a 
join LT_BOX_NAMING b on  b.location = ((a.facility) + '/' + a.zone + '-' + a.row_box + '-' + convert(varchar,a.seat)) 
JOIN deleted i ON b.id_key = i.id_key

it's a bit of a messy trigger. I have three tables a main table lt_box_naming that on insert and update store data and modify fields. it has a location field that location field is the combination of several fields from another table lt_facility_ref

the data looks like this

this is lt_facility_ref
facility    zone       row_box  seat   seat_no  used_customer_no
PRK         Balcony     B       33     17559    8626324
PRK         Balcony     B       34     15889    NULL
PRK         Balcony     B       35     17558    8626324
WZO         Orchestra   D       6      15890    NULL
WZO         Orchestra   D       7      17557    3147711


this is lt_facility_ref
id_key  customer_no type    location           seat_no
1        8626324    I     PRK/Balcony-A-1       17512
2        8626324    I     PRK/Orchestra-B-101   8527
3        3147711    C     PRK/CenterHouse-B-23  8526
4        1235878    I     WZO/TopTier-EE-1      12222

When a record gets inserted into lt_box_naming an insert trigger states using the same update code as above updates used_customer_no to the customer no where this account is saved and that works without issue.

on a very rare occurrence we will need to do the reverse, on delete do the same logic and update the used_customer_no back to NULL to free it up.

The reason for the join was because it was built like this -- the data comes from the lt_facility_ref and gets stored with a significant amount of other data in lt_box_naming

Upvotes: 1

Views: 597

Answers (2)

Brian
Brian

Reputation: 7289

With AFTER DELETE the record is already gone so you can't use it in a join. You can use the already referenced DELETED instead.

   update a
    set used_customer_no = NULL
    -- select a.* , '' as '||||', b.*
    from lt_facility_ref a 
    JOIN deleted i ON i.location =  ((a.facility) + '/' + a.zone + '-' + a.row_box + '-' + convert(varchar,a.seat))

Upvotes: 3

Nik Shenoy
Nik Shenoy

Reputation: 701

Seems like the delete trigger is on lt_box_naming and your UPDATE statement JOIN is also on that table. Since this trigger is AFTER DELETE, that row won't exist.

Can you just remove the reference to LT_BOX_NAMING in the UPDATE statement and use the data from the deleted row to join lt_facility_ref?

Upvotes: 1

Related Questions