Solved

Trigger question..

Posted on 2002-03-27
11
558 Views
Last Modified: 2012-05-04
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
Comment
Question by:Racim BOUDJAKDJI
  • 7
  • 3
11 Comments
 

Expert Comment

by:manonng
ID: 6898816
Hi

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

manon


0
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6898856
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6899273
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6899282
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6899302
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 70 total points
ID: 6899425
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6899451
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6902078
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6902093
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6902094
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
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6902160
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question