I have two tables inside one database called "Category" and "Product" (One-To-Many), and "Cid" is the foreign key for the product table linking to Category.
Now I write a trigger in Category, codes:
USE [MyTest]
GO
/****** Object: Trigger [dbo].[MyTrigger] Script Date: 2012/11/23 16:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[tb_Category]
After Delete
AS
BEGIN
declare @id int
select @id = Id from deleted
print @id
delete from tb_Product where cid=@id
END
And when I wanna execute:
delete from tb_Category where Id=1
I think before category is deleted, the trigger will be triggered! But in fact it comes to an problem of confliction！Why？Can anyone be kind to tell me？
How to fix that？
And what's the MOST difference between "After" and "For" in the trigger？
ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[tb_Category]
Before Delete
AS
Because you have a FK in Product table, so you can't delete categories unless all products under that category are deleted. So product deletion should happen first and then category delete.
Regards,
Snigdha
Please Mark as Answer if my reply helped you.
And what's the MOST difference between "After" and "For" in the trigger？
Thxxxx anyway！
Hi,
See this
FOR | AFTER
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
And can you show us the actual error of this? Probably some constraints/referred keys do prohibit the delete/trigger to fire!
It seems that you need to delete the record in tb_product before delete the record from tb_cateory cause they have ForeignKey relationships.
You could follow below query to alter your trigger and try it again.
USE [MyTest]
GO
/****** Object: Trigger [dbo].[MyTrigger] Script Date: 2012/11/23 16:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[tb_Category]
INSTEAD OF DELETE
AS
BEGIN
declare @id int
select @id = d.Id from deleted d
print @id
delete from tb_Product where cid=@id
PRINT 'Record Deleted -- from tb_product.'
delete from tb_Category where cid=@id
print 'Record Deleted -- from tb_Category.'
END
ToughMan
Participant
1490 Points
635 Posts
Why doesn't Trigger work？
Nov 23, 2012 07:49 AM|LINK
Hi all,
I have two tables inside one database called "Category" and "Product" (One-To-Many), and "Cid" is the foreign key for the product table linking to Category.
Now I write a trigger in Category, codes:
USE [MyTest] GO /****** Object: Trigger [dbo].[MyTrigger] Script Date: 2012/11/23 16:47:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[tb_Category] After Delete AS BEGIN declare @id int select @id = Id from deleted print @id delete from tb_Product where cid=@id ENDAnd when I wanna execute:
I think before category is deleted, the trigger will be triggered! But in fact it comes to an problem of confliction！Why？Can anyone be kind to tell me？
How to fix that？
And what's the MOST difference between "After" and "For" in the trigger？
Thxxxx anyway！
sargamlucy
Member
559 Points
164 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 07:53 AM|LINK
I think you should write before delete
Because you have a FK in Product table, so you can't delete categories unless all products under that category are deleted. So product deletion should happen first and then category delete.
Regards,
Snigdha
Please Mark as Answer if my reply helped you.
ToughMan
Participant
1490 Points
635 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 08:00 AM|LINK
No "Before" key words……:(
eralper
Contributor
6048 Points
971 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 08:10 AM|LINK
Hi ToughMan, you can use "Instead Of" triggers
http://www.kodyaz.com/t-sql/sql-server-instead-of-trigger-with-sequence-table.aspx
SQL Server 2012
sargamlucy
Member
559 Points
164 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 09:29 AM|LINK
Before keyword is there, See the below link
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020224.html
But I am not sure how you can use it...
Regards,
Snigdha
Please Mark as Answer if my reply helped you.
ToughMan
Participant
1490 Points
635 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 11:49 PM|LINK
There's a syntax error……So are you sure it's Microsoft's SQL？
ToughMan
Participant
1490 Points
635 Posts
Re: Why doesn't Trigger work？
Nov 23, 2012 11:53 PM|LINK
Can u explain more about that？
wmec
Contributor
6228 Points
3226 Posts
Re: Why doesn't Trigger work？
Nov 24, 2012 02:50 PM|LINK
Hi,
See this
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
And can you show us the actual error of this? Probably some constraints/referred keys do prohibit the delete/trigger to fire!
HuaMin Chen
ToughMan
Participant
1490 Points
635 Posts
Re: Why doesn't Trigger work？
Nov 25, 2012 02:27 AM|LINK
Error is that ForeignKey_Concurrency problem……
Chen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: Why doesn't Trigger work？
Nov 29, 2012 07:12 AM|LINK
Hi Tough Man,
It seems that you need to delete the record in tb_product before delete the record from tb_cateory cause they have ForeignKey relationships.
You could follow below query to alter your trigger and try it again.
USE [MyTest] GO /****** Object: Trigger [dbo].[MyTrigger] Script Date: 2012/11/23 16:47:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[tb_Category] INSTEAD OF DELETE AS BEGIN declare @id int select @id = d.Id from deleted d print @id delete from tb_Product where cid=@id PRINT 'Record Deleted -- from tb_product.' delete from tb_Category where cid=@id print 'Record Deleted -- from tb_Category.' ENDReference on : http://www.codeproject.com/Articles/25600/Triggers-Sql-Server
Thanks.
Feedback to us
Develop and promote your apps in Windows Store