Help With A Trigger
-
Hi Everyone,
I am new to working with triggers. I want the trigger below to only fire if a particular column is updated. Can anyone tell me if I am on the right track? Can I use the same Update statement on a delete trigger?
CREATE TRIGGER trDB_DOCTYPE_IU_ReplicationDateTime ON DB_DOCTYPE
FOR INSERT, UPDATE
AS
-- Update the Replication date time if DDT_DOCU_PRINT_TEXT is updated.
If Update (DDT_DOCU_PRINT_TEXT )
UPDATE TitleDataReplicationDateTime
SET
TitleDataReceiveDateTime=getdate()
FROM
INSERTED I
INNER JOIN DB_DOCUMNT D ON I.DDT_DOCU_TYPE_CODE=D.DDO_TYPE_CODE
INNER JOIN vwTitleNumbersForDocRegNumber V ON D.DDO_REGIST_NBR=V.DocumentRegistrationNumber
INNER JOIN TitleDataReplicationDateTime TDR ON V.TitleReferenceNumber=TDR.TitleReferenceNumber
GO
-
Yes you're on the right track.
You'll have to change the CREATE TRIGGER statement to FOR INSERT, UPDATE, DELETE. You can then have an ELSE statement and the same UPDATE statement again:
If Update (DDT_DOCU_PRINT_TEXT)
UPDATE.....
ELSE
UPDATE.....
Any INSERT or UPDATE's of DDT_DOCU_PRINT_TEXT will fire the top UPDATE statement and any DELETEs whatsoever will fire the bottom UPDATE statement.
This okay?
-
Looks OK to me
The trigger will fire for any INSERT/UPDATE regardless of DDT_DOCU_PRINT_TEXT update or not
The IF UPDATE test restricts the update to only when DDT_DOCU_PRINT_TEXT is updated
Also remember that not all rows may get updated depending on the INNER JOINS (unless you can guarantee that each join will match)
Can I use the same Update statement on a delete trigger Why? The row has been deleted and besides 'IF UPDATE' cannot be used with DELETE operations
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply