January 14, 2013 at 7:01 pm
Hello all,
I have a new trigger:
declare @sql as varchar(8000);
declare @EmailUpdate as varchar(80)
create table #temp_email_update (email varchar (200), CustomerID INT)
insert into #temp_email_update (email, CustomerID)
select email, CustomerID
from Customer
where CustomerID =
(select max (CustomerID)
from Customer where VatRegistrationID = '' and email <> '')
set @EmailUpdate = (select email from #temp_email_update)
create table #temp_email_update_gers (cust_cd varchar (20))
set @sql = ' SELECT * FROM OPENQUERY ( LIVE, ''select max(cust_cd) from cust where email_addr = ''''' + @EmailUpdate + ''''' '
insert into #temp_email_update_gers
exec (@sql) ;
Update Customer
set VatRegistrationID = (select cust_cd from #temp_email_update_gers)
where customerID = (select CustomerID from #temp_email_update)
--select * from #temp_email_update
drop table #temp_email_update
drop table #temp_email_update_gers
As you can see, it's nothing fancy. Once a new record is inserted, I am grabbing the latest CustomerID and through some OpenQuery, I am updating a customer code. The code by itself works great! When I put it in a trigger however, it does NOT work. Nothing. Nada. Zip.
I even tried placing the code in a SPROC and calling the SPROC from the trigger.
Am I missing something? Any help would be GREATLY appreciated.
Cheers,
D
January 14, 2013 at 7:33 pm
I am not sure what is going on with your trigger.
For debugging dynamic sql, I usually will add a PRINT @sql statement above the EXEC @sql statement and comment out the EXEC to see what is going on and if the variable is populated.
I am wondering if you can get away from using this trigger. If the business is Ok with updating the VatRegistrationID after some interval (10 minutes, 1hour,..) I would look into using a SQL Job and just update the customer table with the link server data if the VatRegistrationID does not match the current VatRegistrationID. THis can be a stored proc called through a job.
January 14, 2013 at 7:56 pm
brad.mason5 (1/14/2013)
I am not sure what is going on with your trigger.For debugging dynamic sql, I usually will add a PRINT @sql statement above the EXEC @sql statement and comment out the EXEC to see what is going on and if the variable is populated.
I am wondering if you can get away from using this trigger. If the business is Ok with updating the VatRegistrationID after some interval (10 minutes, 1hour,..) I would look into using a SQL Job and just update the customer table with the link server data if the VatRegistrationID does not match the current VatRegistrationID. THis can be a stored proc called through a job.
I was originally going to use a job and run it every 5 minutes or so, but we want their customer code updated immediately, because they will,need access to it right after the record is inserted.
It weird. The code works great. It only take about 5 seconds. But once I put it in the trigger, nothing happens. I don't get it.
January 14, 2013 at 8:03 pm
Can you try and use the inserted table?
SELECT email, customerId
FROM Inserted
WHERE inserted.VatRegistrationID = '' and inserted..email <> ''
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply