SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 trigger problem?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
283 Posts
Posted - 09/27/2012 :  14:58:29  Show Profile  Reply with Quote
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
Edited by - akpaga on 09/27/2012 15:15:35

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/27/2012 :  15:34:05  Show Profile  Reply with Quote
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
...
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
283 Posts
Posted - 09/27/2012 :  15:58:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48064 Posts
Posted - 09/27/2012 :  16:51:26  Show Profile  Reply with Quote
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/
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
278 Posts
Posted - 09/28/2012 :  15:59:35  Show Profile  Visit lazerath's Homepage  Send lazerath an AOL message  Reply with Quote
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.
Edited by - lazerath on 09/28/2012 16:00:38
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000