Solved

Trigger question..

Posted on 2002-03-27
11
556 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
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.

 
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

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.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

21 Experts available now in Live!

Get 1:1 Help Now