ok simple SQL trigger school me

[DOGWATCH]

ok I just need a very basic sql trigger and haven't been able to find an example on the net. please help.

here is the structure

table is vendor

namefield create_date update_date
--------- ----------- -----------
joeblow NULL 11/1/2004
janeblow NULL 11/3/2004
jacksmith NULL 11/2/2004


I need a simple trigger on an updating table
vendor. update_date date field. I simple need the trigger to copy the contents of the currently update update_date field and copy the same date into the create date field.

for example on the record joeblow I update the field update_date and then the trigger fired and now create_date also has the same date contents copied it into it. HOWEVER i only want to do the update if the contents of create_date is NULL.

i've gotten a start:

CREATE TRIGGER vendor_u
ON VENDOR
FOR UPDATE
AS
IF UPDATE(last_updated)
BEGIN
IF (SELECT last_update FROM updated

...

ANY IDEAS HOW TO FINISH, PLEASE HELP!

 

[dbomrrsm]

CREATE TRIGGER vendor_u
ON VENDOR
FOR update AS
      BEGIN
      INSERT INTO UPDATE_EMAIL -- new table you create
      SELECT deleted.*, GETDATE() As dTimestamp FROM deleted
END
GO

[blue]DBomrrsm[/blue]

 

[DOGWATCH]

close but I can't have it create a new table. also I am not grabbing the current timestamp.

all I am really doing is copying the contents from column A to column B if I update the word 'cat' into the last_update it needs to copy the word 'cat' into the create_date column. however I need to check to see if a value already exists. if a create_date entry already exists then I don't want to overwrite the field.

I think maybe
CREATE TRIGGER vendor_u
ON VENDOr
FOR update AS
BEGIN
SELECT CREATE_DATE=@CREATEDATE FROM UPDATE
UPDATE VENDOR SET CREATE_DATE = @CREATEDATE FROM UPDATED
END
GO

I don't think that will work but I thinks I am on the right track.

 

[dbomrrsm]

CREATE TRIGGER vendor_u
ON VENDOR
FOR update AS
      BEGIN
      UPDATE VENDOR (create_date)
      SELECT update_date FROM deleted
WHERE VENDOR.namefield = deleted.namefield --if namefield is unique
END
GO

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

sorry above was total trash - long day !!!

CREATE TRIGGER vendor_u
ON VENDOR
FOR update AS
      BEGIN
      UPDATE VENDOR 
      SET create_date = (Select update_date FROM deleted
WHERE VENDOR.namefield = deleted.namefield) --if namefield is unique
END
GO

[blue]DBomrrsm[/blue]

 

[DOGWATCH]

I'm soooooooooooo close but if there is already a value in the create_date field I don't want to overwrite it. How to I filter that out?


CREATE TRIGGER vendor_u
ON VENDOR
FOR update AS

BEGIN

UPDATE VENDOR
SET create_date = (Select last_change FROM deleted
WHERE VENDOR.org_pk = deleted.org_pk)
END

 

[DOGWATCH]

any ideas? this item got burried.

 

[JamesLean]

Try this:

CREATE TRIGGER TR_vendor_U
	ON vendor
	FOR UPDATE
AS

UPDATE vendor
SET create_date = i.last_change
FROM vendor v JOIN inserted i ON v.org_pk = i.org_pk
WHERE v.create_date IS NULL
GO

--James