sql trigger perform update on update

Question

I am trying to write an sql trigger that will update another table whenever an update is executed. This is actually used to replace replication (replication exists on our production environment but not on dev environment because it causes problems on dev). I've written following trigger to achieve this, but it doesn't seem to work and I have no idea why. (Also I have no idea how to debug triggers)

USE [AuditQuestionnaireManagerDemo]
GO
/****** Object:  Trigger [dbo].[CatchUpdateUser]    Script Date: 01/04/2012 09:25:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[CatchUpdateUser]
   ON  [dbo].[User]
   FOR UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    update SharedServicesDemo.dbo.[User]
        set SharedServicesDemo.dbo.[User].Username = Username,
            SharedServicesDemo.dbo.[User].PasswordHash = PasswordHash,
            SharedServicesDemo.dbo.[User].Salt = Salt,
            SharedServicesDemo.dbo.[User].PersonGUID = PersonGUID,
            SharedServicesDemo.dbo.[User].OrganizationGUID = OrganizationGUID,
            SharedServicesDemo.dbo.[User].IsDeleted = IsDeleted,
            SharedServicesDemo.dbo.[User].IsActive = IsActive
        where SharedServicesDemo.dbo.[User].GUID = GUID
    SELECT GUID, ID, Username, PasswordHash, Salt, PersonGuid, OrganizationGUID, IsDeleted, IsActive From Inserted

END

Would be great if anyone could spot the problem here!

This insert trigger did work with the insert/select combo:

SET IDENTITY_INSERT SharedServicesDemo.dbo.[User] ON
INSERT INTO SharedServicesDemo.dbo.[User] (GUID, ID, Username, PasswordHash, Salt, PersonGUID, OrganizationGUID, IsDeleted, IsActive)
SELECT GUID, ID, Username, PasswordHash, Salt, PersonGuid, OrganizationGUID, IsDeleted, IsActive From Inserted
SET IDENTITY_INSERT SharedServicesDemo.dbo.[User] OFF

(Is this MS SQL Server?) What is that `select` supposed to do after the `update`? — Mat, Jan 04 '12 at 08:49
@MAT: I'm assuming the user was trying to use that as the rowset for the update.... — Mitch Wheat, Jan 04 '12 at 08:51
The UPDATE/SELECT combo looks weird. I would expect a `;` after the UPDATE and no SELECT at all. I'm no T-SQL programmer, but in PL/SQL you specify that you want the new values by adding `new.` in front of column names. — Klas Lindbäck, Jan 04 '12 at 08:54
Please refer to this link: http://stackoverflow.com/questions/642822/how-can-i-do-a-before-updated-trigger-with-sql-server Regards — , Jan 04 '12 at 08:58
Please refer to this link: http://stackoverflow.com/questions/642822/how-can-i-do-a-before-updated-trigger-with-sql-server Regards — , Jan 04 '12 at 09:00
@Winstan: not relevant...(perhaps you should delete the duplicate comment, or even both...) — Mitch Wheat, Jan 04 '12 at 09:01

Mitch Wheat · Accepted Answer · 2012-01-04T09:12:08.027

2

Your trigger currently does nothing (you got lucky(unlucky?) in that the syntax compiles but does not do what you intend). Try:

update SharedServicesDemo.dbo.[User] 
set  SharedServicesDemo.dbo.[User].Username = i.Username,
    SharedServicesDemo.dbo.[User].PasswordHash = i.PasswordHash,
    SharedServicesDemo.dbo.[User].Salt = i.Salt,
    SharedServicesDemo.dbo.[User].PersonGUID = i.PersonGUID,
    SharedServicesDemo.dbo.[User].OrganizationGUID = i.OrganizationGUID,
    SharedServicesDemo.dbo.[User].IsDeleted = i.IsDeleted,
    SharedServicesDemo.dbo.[User].IsActive = i.IsActive
From  SharedServicesDemo.dbo.[User]
inner join inserted i ON SharedServicesDemo.dbo.[User].GUID = i.GUID

Ref.: UPDATE

[Might need to alias the second instance of SharedServicesDemo.dbo.[User] ...]

edited Jan 04 '12 at 09:12

answered Jan 04 '12 at 08:55

Mitch Wheat

295,962
43
465
541

Had to change column names Username, PasswordHash etc. to Inserted.Username etc. to make it work as they where ambiguous but it works like a charm now. Can you explain why it does not work like in the insert trigger I added to my first post? – laureysruben Jan 04 '12 at 09:05
Like I explained, you had the syntax wrong, but it compiled and did nothing. – Mitch Wheat Jan 04 '12 at 09:06

sql trigger perform update on update

1 Answers1