Solved

Trigger question..

Posted on 2002-03-27
11
555 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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 69

Accepted Solution

by:
ScottPletcher 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql server query? 6 28
Numeric sequence in SQL 14 38
Test a query 23 19
Convert char to decimal in a SQL Server View 14 23
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now