HOW TO MODIFIE THE LAST MODIFIED ENTRY WITH A TRIGGER ?

Hi,
I need to change the value of a column of a table  when another column has been modified.
what is the good syntax ?

Thanks !
CREATE TRIGGER [MODIF_DEVIS_COMMENTAIRE_INTERNE] ON [dbo].[DEVIS] 
FOR UPDATE
AS
IF UPDATE(COMMENTAIRE_INTERNE)
BEGIN
	UPADTE DEVIS SET COMMENTAIRE_INTERNE SET DATE_MODIFICATION = GETDATE  WHERE /*  HOW CAN I RETRIEVE THE PK OF THE UPDATED ENTRY ???    */
END

Open in new window

bruno_boccaraAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
CREATE TRIGGER [MODIF_DEVIS_COMMENTAIRE_INTERNE] ON [dbo].[DEVIS]

FOR UPDATE

AS

IF UPDATE(COMMENTAIRE_INTERNE)

BEGIN
    UPDATE  d
    SET          COMMENTAIRE_INTERNE = <The value you want to use goes here>,
          DATE_MODIFICATION = GETDATE()
    From    DEVIS d
          Inner Join Inserted i On d.<YourPrimaryKeyGoesHere> = i.<YourPrimaryKeyGoesHere>

END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can find the primary key from inserted or deleted tables; to be frank, i dont really prefer TRIGGERS here, you can probably writr the code to perform the update in the same procedure where you do ur update on the other columsn
0
 
Anthony PerkinsCommented:
Let's try that again:

CREATE TRIGGER [MODIF_DEVIS_COMMENTAIRE_INTERNE] ON [dbo].[DEVIS]

FOR UPDATE

AS

IF UPDATED(COMMENTAIRE_INTERNE)

BEGIN
    UPDATE  d
    SET          COMMENTAIRE_INTERNE = <The value you want to use goes here>,
          DATE_MODIFICATION = GETDATE()
    From    DEVIS d
          Inner Join Inserted i On d.<YourPrimaryKeyGoesHere> = i.<YourPrimaryKeyGoesHere>

END
0
 
Anthony PerkinsCommented:
It was right the first time. :(

Sorry for the multiple posts.
0
 
bruno_boccaraAuthor Commented:
Perfect !!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.