July 11, 2014 at 1:04 pm
I currently have a AFTER UPDATE trigger that looks at a specific column being updated. It works great, but I am now needing to also capture the field data when it is initially created as well. Do I need to build a sperate FOR INSERT trigger to capture the initial column data when inserted, or is it possible to build it into my current AFTER UPDATE trigger? Any suggestions will be greatly appreciated.
Here is my current trigger:
CREATE TRIGGER [dbo].[xResponsible_By] ON [dbo].[PARTS_REQUESTOR]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(RESPONSIBLE_BY)
BEGIN
MERGE xBuyerAssignment AS TARGET
USING ( SELECT dl.DOC_NO AS DOC_NO
,pr.RESPONSIBLE_BY
,pr.UPDATED_USR
,GETDATE() AS UPDATED_DTE
FROM DELETED dl
INNER JOIN PARTS_REQUESTOR pr on dl.DOC_NO=pr.DOC_NO
INNER JOIN GROUP_USERS gu on pr.RESPONSIBLE_BY=gu.U_CODE
WHERE pr.DOC_STATUS IN ('30','31')
AND gu.G_CODE='PVS'
)SOURCE ON TARGET.DOC_NO=SOURCE.DOC_NO
WHEN MATCHED THEN
UPDATE SET
TARGET.RESPONSIBLE_BY=SOURCE.RESPONSIBLE_BY
,TARGET.UPDATED_USR=SOURCE.UPDATED_USR
,TARGET.UPDATED_DTE=SOURCE.UPDATED_DTE
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (DOC_NO, RESPONSIBLE_BY, UPDATED_USR, UPDATED_DTE);
END
July 11, 2014 at 1:35 pm
The logic would need to be a little different for an insert trigger. You can do some checking in a trigger to determine what kind of action it is but I prefer to keep them separate. It makes things a bit easier for maintenance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2014 at 2:08 pm
I appreciate the feedback. I will move forward keeping them seperate.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply