Trigger question for an Update trigger

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger question for an Update trigger

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2005-02-23 : 22:21:43

Hi there,

I have the script below that updates some parameters based on the Inserted value in a trigger, those values are then supplied to a stored proc which updates another database table (the idea is to keep 2 disparate members databases in synch). I have tested this by modifying one user at a time and everything seems to work, however if multiple rows are updated do these parameters get updated for each row and the stored procedure called for each rows update?

CREATE TRIGGER tr_Update_Subscriber ON dbo.tbl_Subscriber
FOR UPDATE
AS

DECLARE @FNAME varchar(255)
DECLARE @SURNAME varchar(255)
DECLARE @EMAIL varchar(255)
DECLARE @OLDEMAIL varchar(255)
DECLARE @PASSWORD varchar(50)
DECLARE @EXTERNALID int

SELECT
@FNAME = INSERTED.Givenname,
@SURNAME = INSERTED.SURNAME,
@EMAIL = INSERTED.EMAIL,
@PASSWORD= INSERTED.PASSWORD,
@EXTERNALID = INSERTED.SUBSCRIBERID
FROM INSERTED

--get the old email from the deleted temp table
SET @OLDEMAIL = (SELECT DELETED.EMAIL FROM DELETED WHERE DELETED.SubscriberID =@EXTERNALID)

--Execute the stored procedure
EXEC vividnrl.dbo.p_UPDATE_EXTERNAL_subscriber @FNAME,@SURNAME,@EMAIL,@OLDEMAIL,@PASSWORD,null,@EXTERNALID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-23 : 22:53:08

No, the way you have it now won't work for multiple record updates.

Is there a reason you want to update the vividnrl version of the table via an SP?

The simplest change would be to eliminate the SP and just code the trigger as you normally would with the target table in the same database.
ie: (JOINing to inserted to capture multi-record update values)

Update a set
a.<cols> = b.<cols>
From vividnrl.dbo.tbl_subscriber a
JOIN inserted b ON a.subscriberid = b.subscriberid

Another option is to not use a trigger and set up replication between the 2 databases. Especially if the databases will ever be on different servers.

Be One with the Optimizer
TG

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2005-02-23 : 23:06:31

thanks for your help.

the trigger now reads:

UPDATE vividnrl.dbo.tblMembers
SET
FirstName = INSERTED.GIVENNAME,
Surname = INSERTED.SURNAME,
username = INSERTED.EMAIL,
PASSWORD = INSERTED.PASSWORD
FROM INSERTED
INNER JOIN DELETED ON DELETED.SUBSCRIBERID = INSERTED.SUBSCRIBERID
WHERE vividnrl.dbo.tblMembers.EXTERNALID = INSERTED.SubscriberID And vividnrl.dbo.tblMembers.Username = DELETED.EMAIL

Now I have a new issue, if this sql statement returns an error (i.e. a duplicate value is entered in the email column on tblMembers) the entire Insert will fail. Is there a way to allow the insert to continue if there is an error in the above statement?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-24 : 07:35:13

If a trigger generates an error then the entire statement is rolled back.

Do you have a unique constraint on the table with the trigger (email column)? or the table you are updating (username)?
if the error is being generated on the table with trigger, then it's not the trigger that's failing. However, if the error due to a unique constraint on the table you are updating (username column), wouldn't you want the statement to fail? I thought the whole idea was to keep the tables in sync. If you don't mind the tables getting out of sync, you could test for the existance of a potential duplicate value and if exists, don't do the update. But I think that defeats your purpose of the trigger, doesn't it?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources