Think you should add the Keyfields in the where clause of your update. Your
update will scan the whole table DOCSADM.PROFILE. Maybe this table is very
large, so the trigger won't come to an end or you habe a locking situation.

UPDATE DOCSADM.PROFILE
SET document_date = @MYDATE
WHERE document_date = 'JAN 1 1753'

Why converting GETDATE() in MYSTRING and reconvertz in MYDATE?

UPDATE DOCSADM.PROFILE
SET document_date = GETDATE()
WHERE document_date = 'JAN 1 1753'
AND Keyfields=...

HTH

Regards

Volker Schoen
Tel.: +49 208 / 65 91 - 950
Fax: +49 208 / 65 91 - 980
E-Mail: mailto:v.[Email Address Removed]
http://www.inplan.de



-----Ursprüngliche Nachricht-----
Von: [Email Address Removed] [mailto:[Email Address Removed]
Gesendet: Donnerstag, 16. Oktober 2003 18:02
An: LazyDBA.com Discussion
Betreff: SQL trigger.


Good day everyone!

I have a problem with a trigger.

I created a trigger to insert the current date into a filed if no date as
been specified. For some reason it does not work.

Here is how it look:

CREATE TRIGGER GETDATE ON docsadm.PROFILE
FOR INSERT,UPDATE
AS

UPDATE DOCSADM.PROFILE
SET DOCNAME = 'Document No.' + ' ' + CONVERT(varchar(24), (DOCNUMBER))
WHERE DOCNAME = ''

DECLARE @MYDATE DATETIME
DECLARE @MYSTRING VARCHAR(11)
SELECT @MYSTRING = CONVERT(VARCHAR, GETDATE())
SELECT @MYDATE = CONVERT(DATETIME, @MYSTRING)

UPDATE DOCSADM.PROFILE
SET document_date = @MYDATE
WHERE document_date = 'JAN 1 1753'

Can anyone tell me why it does not work?
If I place the trigger in it freeze the application.



Thanks




MS Sql Server LazyDBA home page