| Author |
Topic |
|
jbreslow
Starting Member
16 Posts |
 Posted - 2011-03-02 : 13:37:23
|
| Hello,How can I pass a value that was used in a query's where clause into a Trigger?If I run the following query in SQL Manger, how do I get the ID (590) that was passed to the query into the Trigger? I need this ID so I know which record the Trigger is going to update, right?update user_addressset address1 = '1234 Some Street'where id = 590What I am trying to do with the Trigger is to copy any changes that were made in one table to another table. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-02 : 13:39:30
|
| You can't grab the value from the DML query, but you really don't need it as the deleted/inserted trigger tables contain the data that you'll need to copy it to another table. The deleted trigger table will contain the "before" image of the affected rows.The inserted trigger table will contain the "after" image of the affected rows.Read up on these trigger tables for more information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-02 : 14:53:44
|
| Hi tkizer,Thank you for the quick reply. So, if I don't need to pass these values in, then how do I update the correct row in the second table? Do you have sample code you can share?Also, I am trying to trigger this on UPDATE not DELETE of INSERT.I have been reading up on triggers and I am still lost, which is why I posted to these forums. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-02 : 15:05:51
|
| Hello,I added that code to my Trigger, ran it, and it seemed to work. Although, when it ran it said that all 3000 records in my table were updated (but only one record actually changed). Running through each record seems like a lot of over-head when only one record was updated. Is there a way to only update the record that was changed when the Trigger was executed? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-02 : 15:19:18
|
| Table 1 and Table 2 are exact copies of each other (don't ask). So yes, every row in Table 1 matches a row in Table 2 but that doesn't mean I want to update each and every record in the table. Just the record in Table 1 that was updated is all that needs to be updated in Table 2. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-02 : 15:55:52
|
| Here is my code for the Trigger...ALTER TRIGGER [dbo].[trigg_user_address] ON [dbo].[USER_ADDRESS]AFTER UPDATE ASDECLARE @ua int;DECLARE @uam int;-- count columns in user_address tableSELECT @ua = (select count(col.name) as num_columnsfrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS')-- count columns in user_address_multi tableselect @uam = (select count(col.name) as num_columns_multifrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS_MULTI')-- make sure the columns from the two tables matchIF @ua = @uam -- if they match, then update the second table BEGIN UPDATE user_address_multi SET uam.address1 = ua.address1 FROM user_address ua JOIN user_address_multi uam ON ua.user_addr_id = uam.user_addr_id ENDELSE BEGIN -- if they don't match, raise error and rollback update RAISERROR ('Columns do not match, change was not committed', 11,1) ROLLBACK ENDThe two tables have the exact same columns...user_addr_id (PKID)user_fkid (FKID)address1address2address3citystatezipaddress_type_id |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-03 : 10:29:46
|
| Sorry about that. I read that line as just an example table name. I changed it back and all worked fine, one record updated. Do you happen to have a good resource for learning about Triggers and things like 'inserted'?UPDATE t2SET address1 = i.address1FROM inserted iJOIN user_address_multi t2ON i.user_addr_id = t2.user_addr_idThank you so much for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-09 : 13:39:32
|
| Do you happen to have an example of a Delete query for a trigger? I am trying the following and I am getting an error...delete from user_address_multiwhere user_address_multi.user_addr_id = deleted.user_addr_idError:The multi-part identifier "deleted.user_addr_id" could not be bound. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-09 : 14:01:21
|
| Once again, you are my savior!!!!Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Pass value from SQL to Trigger
Tara Kizer