DELETE TRigger in SQL table

Question

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

Answer 1

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

Answer 2

Hi @Brindha Thangavel ,

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:

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.