| Author |
Topic |
|
coldfiretech
Starting Member
30 Posts |
 Posted - 2009-04-02 : 16:27:45
|
Hello Everyone, this is my first post here.Im pretty new to SQL Server so im kinda shooting in the dark here.Im trying to write a trigger that will delete some other rows from several other tables. The problem is that when I try to delete more than row row in the table that has the trigger it gives me an error. How can i perform these action for each row in the virtual table?ALTER TRIGGER [dbo].[trig_delWorkAddress] ON [dbo].[csWork_Addresses_Xref]FOR DELETE ASBEGIN DELETE FROM csAddresses WHERE ID in (SELECT AddressID FROM Deleted)END Does anyone know how I can make this fire for each row??Ive been bangning my head for days trying to fix this.~MattSucess comes before work only in the dictionary. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-02 : 16:33:44
|
| What is the error you are getting? Rather than a trigger you can usually accomplish this by creating a foreign key constraint with cascade delete.Be One with the OptimizerTG |
 |
|
|
coldfiretech
Starting Member
30 Posts |
Posted - 2009-04-02 : 16:36:59
|
| Subqueries only allow one row to be returned when it “follows =, !=, <, <= , >, >= or when the subquery is used as an expression“Can someone just show me an example of how to do this with a trigger?Sucess comes before work only in the dictionary. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-02 : 16:54:08
|
| The code you posted can not return that error - it looks fine. Are there other triggers on the table?Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-02 : 16:57:43
|
Look for code like this in any of the delete trigger code:DELETE FROM csAddresses WHERE ID in = (SELECT AddressID FROM Deleted)Be One with the OptimizerTG |
|
|
|
coldfiretech
Starting Member
30 Posts |
Posted - 2009-04-02 : 17:11:29
|
| okay, maybe i grabbed the wrong trigger. heres a better explaination of my problem..i have a work table, work address_xref and work phonenumbers_xref tables. as well as addresses and phonenumbers table.when i go to delete a profile if it has more than one job attached to it i need it to delete all jobs, and when the job is deleted i need to to delete the corrosponding address and phone number. i tried setting up the cascade action on delete for the columns in the xref tables but it when i deleted a job it only deleted the job, and it rows out of the xref tables not the phone number or the address.again, im a complete rookie here, no schooling anything just trying to teach myself. Sucess comes before work only in the dictionary. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-02 : 17:28:59
|
| The cascade delete just works between two tables, you need to establish similar FK relationships with cascade delete between all table/columns that apply. ie: profile->workaddress and workaddress->addressesBut to simply fix your error: Subqueries only allow one row to be returned when it “follows =, !=, <, <= , >, >= or when the subquery is used as an expression“just look through your trigger code for the condition I told you about (above). Replace the "=" with "IN".Be One with the OptimizerTG |
|
|
|
coldfiretech
Starting Member
30 Posts |
Posted - 2009-04-02 : 17:29:12
|
| Okay, i found the problem trigger, replaced it and things seem to be working properly.Sucess comes before work only in the dictionary. |
|
|
|
coldfiretech
Starting Member
30 Posts |
Posted - 2009-04-02 : 18:46:12
|
| Thank you TG for your help.. I will look into why my cascade delete isnt working between the xref tables and the addresses/phonenumbers tables.I appreciate your help!~MattSucess comes before work only in the dictionary. |
|
|
|
|
[RESOLVED] Problem with Delete Triggers