question

BrindhaThangavel-5098 avatar image
0 Votes"
BrindhaThangavel-5098 asked MelissaMa-msft answered

DELETE TRigger in SQL table

Hi All..I have created SQL trigger for delete statement in SQL table.Whenever I delete rows in table my DELETE trigger is not working and I am using below statement for my trigger

"DELETE FROM table1 WHERE AttributeKey=81 AND MemberKey IN(SELECT ma.MemberKey FROM table1 ma INNER JOIN Deleted D ON ma.value=D.Number AND ma.AttributeKey=2) "

Deleted table have column--D.number
table1 have column -Id,attributekey,memberkey,value

Thanks,
Brindha

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day and welcome to the QnA forums

I have created SQL trigger for delete statement in SQL table.

We cannot read minds or connect your machine/server. Stories do not help us. If you say that you did something that we need to be able to do the same

Please provide the queries to create your relevant table(s) and insert some sample data, and provide the queries to create the trigger

Whenever I delete rows in table my DELETE trigger is not working

All the triggers which you present in the question (which is non) working great :-)

Please provide the missing information to reproduce the issue

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @BrindhaThangavel-5098,

Welcome to Microsoft Q&A!

Could you please provide some sample data and expected output?

Besides, in your delete trigger, there are two conditions (AttributeKey=81 and ma.AttributeKey=2) which are contradictory. Are these conditions any specific meaning or rule? Please kindly provide more details about it.

I created two tables and inserted some sample data as below:

 create table table1
 (ID int,
 attributekey int,
 memberkey int,
 value int)
    
 insert into table1 values
 (1,81,11,20),
 (2,81,12,21),
 (3,2,11,22),
 (4,2,12,23),
 (5,7,13,22),
 (6,9,13,24)
    
 create table tabledelete
 (number int)
    
 insert into tabledelete values
 (20),(21),(22),(23),(24)

Then I created the trigger as you provided.

 create trigger mydeletetrigger on tabledelete 
 after delete 
 as 
 DELETE FROM table1 
 WHERE AttributeKey=81 AND MemberKey IN
 (SELECT ma.MemberKey FROM table1 ma 
 INNER JOIN Deleted D 
 ON ma.value=D.Number AND ma.AttributeKey=2) 

Fire this trigger and validate as below:

 delete from tabledelete where number=22
    
 select * from tabledelete
 select * from table1

Output:
136102-out1.png
As you could see, only the row of AttributeKey=81 was deleted automatically.

If the conditions (AttributeKey=81 and ma.AttributeKey=2) are not specific ones and you would like to delete all the related rows which has the same MemberKey which is related with the deleted number from tabledelete, you could refer below trigger and check whether it is helpful.

 create trigger mydeletetrigger on tabledelete 
 after delete 
 as 
 DELETE FROM table1 
 WHERE MemberKey IN
 (SELECT ma.MemberKey FROM table1 ma 
 INNER JOIN Deleted D 
 ON ma.value=D.Number) 

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


out1.png (4.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.