September 29, 2009 at 9:44 am
Hi! again 😀
table A has data i need to delete. But this data is refered to another table; B.
So i need to create a trigger that deletes from B when a delete on A.
I created the trigger but it doesn´t work. (it doesn´t surprise me:w00t:)
the super trigger:
create trigger [delcascadetrig]
on [softland].[iw_tprod]
for delete
as
delete softland.nw_detnv
from softland.nw_detnv, deleted
where softland.nw_detnv.codprod = deleted.codprod
I feel like im missing a really easy thing.
thanks!!.
September 29, 2009 at 9:53 am
This should work, providing that you have the proper columns linked. Note that you're writing an old style join, which is not recommended. You ought to write something more like:
create trigger [delcascadetrig]
on [softland].[iw_tprod]
for delete
as
delete n
from softland.nw_detnv n
inner join deleted d
on n.codprod = d.codprod
Is codprod the FK between these tables?
What doesn't work in your testing? Meaning what isn't deleted.
September 29, 2009 at 9:58 am
Steve Jones - Editor (9/29/2009)
This should work, providing that you have the proper columns linked. Note that you're writing an old style join, which is not recommended. You ought to write something more like:
create trigger [delcascadetrig]
on [softland].[iw_tprod]
for delete
as
delete n
from softland.nw_detnv n
inner join deleted d
on n.codprod = d.codprod
I´ll improve my writing with practice and advice. Thanks!
Is codprod the FK between these tables?
Yes it is.
What doesn't work in your testing? Meaning what isn't deleted.
Once i created de trigger i tested it and i got reference error.
"DELETE statement conflicted with the REFERENCE constraint "IW_TProd_NW_DetNV01. The conflict has appeared in the database "softdesa" Table "softland.nw_detnv", column 'CodProd'."
September 29, 2009 at 10:18 am
You are trying to delete rows that have a FK reference. You need to delete the child rows before you can remove the parent.
September 29, 2009 at 10:31 am
Steve Jones - Editor (9/29/2009)
You are trying to delete rows that have a FK reference. You need to delete the child rows before you can remove the parent.
ok...so i have to erase the data that is fk first.
September 29, 2009 at 10:47 am
Yes, but be sure this is what you want. In a FK, you have to remove all children before you can remove a parent. Note that you might have multiple tables linked by FKs, and that is why your trigger is failing.
September 29, 2009 at 11:00 am
Steve Jones - Editor (9/29/2009)
Yes, but be sure this is what you want. In a FK, you have to remove all children before you can remove a parent. Note that you might have multiple tables linked by FKs, and that is why your trigger is failing.
thanks im solving it using delete with sub select.
Like you said it has found more that one table referenced.
thanks.!
October 1, 2009 at 12:13 pm
Would a simple Cascade Delete Foreign Key Relationship not work?
October 1, 2009 at 12:54 pm
Chim Kalunta (10/1/2009)
Would a simple Cascade Delete Foreign Key Relationship not work?
would you be so kind to post an example.
October 1, 2009 at 1:29 pm
Sure.
ALTER TABLE [dbo].[ForeignKeyTable] WITH CHECK ADD CONSTRAINT [FK_ForeignKeyTable_PrimaryKeyTable] FOREIGN KEY([col_fk])
REFERENCES [dbo].[PrimaryKeyTable] ([col_pk])
ON DELETE CASCADE
The key clause in the code above is the ON DELETE CASCADE. It is one of a number of options you can create a Foreign Key relationship with.
In this case, any row deleted from the Primary Key table would automatically cause the delete of its child rows.
You will need to make sure that this is a business rule you do want to implement bearing in mind its obvious implications.
October 5, 2009 at 3:37 pm
Chim Kalunta (10/1/2009)
Sure.
ALTER TABLE [dbo].[ForeignKeyTable] WITH CHECK ADD CONSTRAINT [FK_ForeignKeyTable_PrimaryKeyTable] FOREIGN KEY([col_fk])
REFERENCES [dbo].[PrimaryKeyTable] ([col_pk])
ON DELETE CASCADE
The key clause in the code above is the ON DELETE CASCADE. It is one of a number of options you can create a Foreign Key relationship with.
In this case, any row deleted from the Primary Key table would automatically cause the delete of its child rows.
You will need to make sure that this is a business rule you do want to implement bearing in mind its obvious implications.
thanks man!
October 5, 2009 at 3:42 pm
You are welcome!
October 6, 2009 at 4:02 am
A word of warning about using DELETE CASCADE if you are using transactional replication.
I don't know if this is still relevant to SQL 2005, but it certainly happened in SQL 2000.
If you update a row on the publisher, and make changes to any column that participates in the PRIMARY KEY or a UNIQUE constraint, that change is actually written to the transaction log as a DELETE/INSERT pair (you may be able to see where this is going already). When the log reader agent scours the transaction log, it can match up the DELETE/INSERT pair and replicate this as an UPDATE, but...
If the update statement affected more than 1 row, it is written to the transaction log as DELETE, DELETE... INSERT, INSERT... and the log reader replicates these as DELETEs followed by INSERTs. If you have a DELETE CASCADE constraint, you will suddenly find that the child rows have disappeared from your subscriber.
Make sure you mark the constraint as NOT FOR REPLICATION, or don't cascade deletes at all.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply