Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2008-09-10 : 13:20:20
|
| I created a trigger that works great for insert functionality. It fires when a new record is inserted into a table by creating a new record in a (different) table. I need to modify my trigger to work both for an insert and for an update. Here's the script that works for INSERT:CREATE TRIGGER [dbo].[X_TEC_tr_InsertCustItems] ON [dbo].[SOP60300] AFTER INSERTAS declare @itemnmbr char(31)declare @custnmbr char(15)declare @custprice char (21)declare @custpartnmbr char (31)declare @dex_row_id int declare @itemdesc char(101)declare @stndcost numeric (19,5)declare @cust_lead_time char (21)declare @itemweight int Select @itemnmbr = ITEMNMBR, @custnmbr = CUSTNMBR, @custprice = USERDEF1, @custpartnmbr = CUSTITEMNMBR, @dex_row_id = DEX_ROW_ID, @itemdesc = CUSTITEMDESC, @cust_lead_time = USERDEF2 from inserted///get data from item master//////BEGIN Select @stndcost = STNDCOST, @itemweight = ITEMSHWT from IV00101 WHERE ITEMNMBR = @itemnmbr INSERT INTO dbo.X_TEC_CUSTNMBR_XREF ( ITEMNMBR,CUSTNMBR,CUSTPRICE,CUSTPARTNMBR, DEX_ROW_ID, ITEMDESC, STNDCOST, CUST_LEAD_TIME, ITEMWEIGHT ) Select @itemnmbr, @custnmbr, @custprice, @custpartnmbr, @dex_row_id, @itemdesc, @stndcost, @cust_lead_time, @itemweight END The UPDATE should kick in if the @itemnmbr and @custnmbr combination exists in the X_TEC_CUSTNMBR_XREF table already - if that combination doesn't exist, then the insert kicks in..... I think I need a 'IF EXISTS' statement - but I don't know where to put it. I tried placing the if exists before the select statement - that obviously didn't work because the variable @itemmnbr had not been declared. So, I tried placing it right before the BEGIN and couldn't get it to work also. Any assistance would be appreciated......Thanks in advance for reviewing my post. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Trigger question -