Trigger help

[dgillz]

I am trying to create an update trigger that will update a field if another field's value is 'Y'. I want the field to be populated with the current date-time. Here is my code so far:

CREATE TRIGGER ReadyToPickDateTimeStamp ON [dbo].[oeordhdr_sql] 
FOR UPDATE
AS

if user_def_fld_2 = 'Y' then  SET user_def_fld_3 = 'A'

I am getting an error "incorrect syntax near the keyword 'then'". Also I would like an expression to replace "A" with the current date-time.

Any insights appreciated.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[VintageWine]

1. SQL Server does not require "then" as part of "if" statements.

2. In triggers there two cursors available "Inserted" and "Deleted", each have the same fields as the table in question. It is from these you get the values. An update will have a record in both cursors.

3. You cursor handles updates. What about inserts?

4. GETDATE() function privides current date

Maybe you want something like this:

CREATE TRIGGER ReadyToPickDateTimeStamp ON [dbo].[oeordhdr_sql]
FOR UPDATE, INSERT
AS

UPDATE O 
SET user_def_fld_3 = GETDATE()
FROM [dbo].[oeordhdr_sql] O
JOIN Inserted I ON I.oeordhdrID = O.oeordhdrID
LEFT JOIN Deleted D ON D.oeordhdrID = O.oeordhdrID
WHERE
I.user_def_fld_2 = 'Y' AND ISNULL(D.user_def_fld_2, '') <> 'Y'

Be sure to have recursive triggers not checked for the database.

Have fun
Simon

 

[dgillz]

Simon,

Thanks for the help, that got me going in the right direction. Here is my current code:

CREATE TRIGGER ReadyToPickDateTimeStamp ON [do].[oeordhdr_sql] 
FOR UPDATE AS

UPDATE oeordhdr_sql
SET user_def_fld_3 = convert(varchar,getdate(),101)+' '+convert(varchar,getdate(),108)

WHERE user_def_fld_2='Y'

This works fine, however this also updates the date time stamp for ANY change to the record if the user_def_fld_2="Y". The other condition is I only want to update this when the user_def_fld_3 is not already populated. So I added at the end of the above code:

and len(ltrim(user_def_fld_3))=0

However this is not working on nulls. How can I test for nulls?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[VintageWine]

Dgillz,

Please read books online for CREATE TRIGGER. Your code will update every record in the table where user_def_fld_2='Y', not only the record that has just been changed. You must use "Inserted" cursor to identify the changed records.

To include test for null records:

........ AND user_def_fld_3 IS NULL

Simon

 

[dgillz]

Simon,

I expect it to update every record. The null test will fix that. I was struggling with the code for the null test.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[SQLSister]

dgillz, you really don't want a trigger to update every record in the database every time something is changed. This is what will happen:

Thefirst time it runs, it will update the 10000 records that meet the criteria. The next time a record is updated it will update that record and those other 9999 records which already have the correct data. This puts a huge load onthe server over time and will probably result in timeouts at some point.

Id you need to update every record to begin with to get the existing data correct, do not do that in trigger, simply run the same type of query from Query analyzer. Then write the trigger so that it updates only the records which were changed. To do this you use the inserted pseudotable to identify the records.

Questions about posting. See faq183-874