?
Solved

Trigger question..

Posted on 2002-03-27
11
Medium Priority
?
562 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 280 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

771 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