• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Trigger question..

Hi all,


Should not be too difficult to answer for somebody who already did it once.  I want to create a trigger on a particular field (let's say field1) on a table (let's say table1). Field1 is in column 8 of table1.  To be more precise each time this fiel is updated, I need to write down the system date on another field (let's say field2) in the SAME row that has been updated.  The field2 (datetime field) is in column 12 of Table1.

Can any body get me a CREATE TRIGGER statement to do the job OR suggest me of another maybe smarter way.

Sincerely,

Racimo

Sincerely,
0
Racim BOUDJAKDJI
Asked:
Racim BOUDJAKDJI
  • 7
  • 3
1 Solution
 
manonngCommented:
Hi

Is this the case then you don't need to use trigger, just defined column 12 as 'timestamp'.

manon


0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
manonng,

I thought about doing that...
If I set up a timestamp on col 12, this will fire at each INSERT.  In this case, I need the time stamp on a particular UPDATE event.  Therefore, when somebody updates the field1 to yes then and only then I want the field2 to be updated to the time stamp.  Before that, the field2 is null.

Sincerely,

Racimo
0
 
Scott PletcherSenior DBACommented:
Something like this should work:

CREATE TRIGGER trig_name
ON TABLE table_name
FOR UPDATE
AS
IF UPDATE(field1)
    UPDATE table_name
    SET field2 = GETDATE()
    FROM table_name
    INNER JOIN deleted d ON table_name.key1 = d.key1 --AND table_name.key2 = d.key2 ...
    WHERE table_name.field1 = 'Y' AND d.field1 <> 'Y'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott PletcherSenior DBACommented:
Brief notes on the trigger code:

You would expect that the "IF UPDATE(field1)" checks to see if that field was updated, but that's not 100% true.  It actually only checks to see if that field was mentioned in the UPDATE statement.  That's the reason for the WHERE condition -- to make sure that the value actually changed to 'Y' from something else.  The deleted table contains the rows before they were updated, so a comparison to d.field1 is to the value BEFORE the update occurred.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
ScottPletcher,

Thanks for the reply.  Do you mean that d is some kind of system table that contains the older version of record B4 commiting the transaction?  Does this syntax apply on both SQL Server 7 and SQL Server2000 without variations?

Sincerely,

Racimo
0
 
Scott PletcherSenior DBACommented:
Yes, the syntax should work on SQL 7.0 and 2000.

Actually the system table name is deleted, but I created an alias of  d  (INNER JOIN deleted d --d becomes synonym for deleted) to shorten typing, a very common technique.

Be sure to replace key1 (and, if necessary, key2, etc.) with the unique identifiers for the table; the rows have to be matched up properly for a trigger like this to work.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
ScottPletcher,

Sorry if I seemed doubtfull.  In fact, I was expecting another approach using ....  CREATE TRIGGER... COLUMN_UPDATED()... and problem was to find out a criteria to isolate the record on.  I'll give it a shot and get back to you tomorrow.

Sincerely,

Racimo
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
ScottPletcher,

After adapting the script to create the trigger a stored procedure, I tried to save the procedure but I receive a strange message saying I can not change object type within a script.  To get back to the example:

My table name is called 'COMMANDES2'
Field1 is called 'traitee'.  It is a boolean value
Field2 is called 'commande_date_FDT' .  It is a datetime value.
Key1 is called 'id_commande'.  It is an varchar value.
I tried to save the following script:

CREATE TRIGGER update_date_fdt
ON TABLE COMMANDES2
FOR UPDATE
AS
IF UPDATE(traitee)
UPDATE COMMANDES2
SET date_FDT = GETDATE()
FROM COMMANDES2
INNER JOIN deleted d ON COMMANDES2.id_commande = d.id_commande WHERE COMMANDES2.traitee = 1 AND d.traitee <> 1

What's wrong?

Sincerely,

Racimo
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
I also get an error message saying

<<ADO Error: Incorrect syntax near the keyword 'TRIGGER'  Can only use IF UPDATE within a CREATE TRIGGER statement>>

That was associated with a create procedure statement.

Sincerely,

Racimo
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
I also get an error message saying

<<ADO Error: Incorrect syntax near the keyword 'TRIGGER'  Can only use IF UPDATE within a CREATE TRIGGER statement>>

That was associated with a create procedure statement.

Sincerely,

Racimo
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
ScottPletcher,

Sorry, I finally get it to work ignore all my previous remarks.  Thank you very much for your help.

Sincerely,

Racimo
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now