CODEWITHSUNDEEP
sql-servert-sqltriggerssql-server-2008-r2
daniele3004
daniele3004

Reputation: 13970

SQL Server : trigger AFTER INSERT,UPDATE

In my database I have this trigger, it works but it looks really bad as a code.

The table HOURS contains an idWork historicized when user register your activity. Each user have assigned a personal idWork

CREATE TRIGGER [dbo].[trg_HOURS_002]
   ON [dbo].[HOURS]
   AFTER INSERT,UPDATE
AS 
BEGIN
    Update [HOURS] 
    set idWork = (select U.idWork 
                  from [USER] U
                  where U.id = (select inserted.idUser from inserted))
     where id = (select inserted.id from inserted)  
END
GO

Can you help me make it better?

Upvotes: 0

Views: 114

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Try changing your sub-query to JOIN. Something like this.

UPDATE A
SET    idWork = b.idWork
FROM   [HOURS] A
       JOIN (SELECT U.idWork,
                    i.id
             FROM   [USER] U
                    JOIN inserted I
                      ON U.id = i.idUser) B
         ON a.id = b.id

Or 

UPDATE A
SET    idWork = b.idWork
FROM   [HOURS] A
        JOIN inserted I
            ON a.id = I.id
        JOIN [USER] U
            ON U.id = i.idUser

Upvotes: 1

Related Questions