trigger problem? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

trigger problem?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-27 : 14:58:29

I am using the following query to upsert my table Customers

DECLARE @offset INT
select @offset=isnull(max(RowID),0) from Customers
MERGE Customers AS C
USING (SELECT ROW_NUMBER() OVER ORDER BY ID) AS Seq,*
FROM NewCustomers) AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName,RowID) VALUES (NC.LastName,NC.FirstName,NC.Seq +@offset);

The Problem with above statement is that I want to update the firstname only when Firstnames for a lastname has been changed but the above statement updates irrespective of it...

i want to create a trigger on my Customers table that would log the values of
Last_name,FirstName_Old(old value),Firstname_New( updated value),logdate(getdate())

This is how i have it but does not seem to work when i use the above Query for Update..It logs only the first row.

CREATE TRIGGER trgAfterUpdate ON dbo.Customers
After UPDATE
AS
declare @LastName nvarchar(100);
declare @FirstName_Old varchar(100);
declare @FirstName_New varchar(100);

select @LastName= i.@LastName from inserted i;
select @FirstName_New=i.@FirstName_New from inserted i;
select @FirstName_Old= d.@FirstName_Old from deleted d;

insert into Customers_Audit(LastName,FirstName_Old,FirstName_New,LogDate)
values(@LastName,@FirstName_Old,@FirstName_New ,getdate());

PRINT 'AFTER UPDATE Trigger fired.'
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 15:34:05

Trigger gets called only once per update regardless of the number of rows affected. That is why you are seeing the behavior you are seeing. So what your trigger code should be something like this:

CREATE TRIGGER trgAfterUpdate ON dbo.Customers
After UPDATE
AS
insert into Customers_Audit(LastName,FirstName_Old,FirstName_New,LogDate) 
SELECT
	i.LastName,
	d.Firstname,
	i.FirstName,
	GETDATE()
FROM
	INSERTED i
	FULL JOIN DELETED d ON d.Lastname = i.Lastname;

Apart from the question of the trigger misbehaving, couldn't you change your merge statement as shown below to update only if first names are different?

....
ON C.LastName = NC. LastName
WHEN MATCHED AND C.FirstName <> NC.FirstName THEN
UPDATE 
SET    C.FirstName = NC.FirstName
...

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-27 : 15:58:47

Thank you Sunita..That was very helpful ...As for the below statement
I figured it out for myself..
ON C.LastName = NC. LastName
WHEN MATCHED AND C.FirstName <> NC.FirstName THEN
UPDATE
SET C.FirstName = NC.FirstName

have a good day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 16:51:26

I hope FirstName is not a nullable field (I think it should be defined as NOT NULL but worth checking once)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-09-28 : 15:59:35

Is this production code for a business? Using a person's last name as the key for a database table is an incredibly inflexible design. It is statistically very common for more than one person to share a last name in even a small data set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources