![]() |
---|
/* Generated by Blackbaud AppFx Platform Date: 07/21/2011 1:13:00 PM Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null Copyright Blackbaud */ CREATE TRIGGER dbo.TR_PDACCOUNTSTRUCTURE_AUDIT_UPDATE ON dbo.PDACCOUNTSTRUCTURE AFTER UPDATE NOT FOR REPLICATION AS declare @ChangeAgentID uniqueidentifier declare @AuditKey uniqueidentifier declare @AuditDate datetime declare @DateChanged datetime set nocount on set @DateChanged = null set @ChangeAgentID = null -- make sure the datestamp and changeagent fields are updated if not update(CHANGEDBYID) begin --Get a default changeagent id. Applications should always explicitly included CHANGEDBYID in any updates to avoid a default change agent id. exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTFROMCONTEXT @ChangeAgentID output IF not update(DATECHANGED) begin -- neither datechanged nor changedbyid were updated, so update both set @DateChanged = GetDate() update dbo.PDACCOUNTSTRUCTURE set CHANGEDBYID = @ChangeAgentID, DATECHANGED = @DateChanged from dbo.PDACCOUNTSTRUCTURE inner join INSERTED on PDACCOUNTSTRUCTURE.ID = INSERTED.ID end else -- date changed was updated, but changedbyid wasn't so just update changedbyid update dbo.PDACCOUNTSTRUCTURE set CHANGEDBYID = @ChangeAgentID from dbo.PDACCOUNTSTRUCTURE inner join INSERTED on PDACCOUNTSTRUCTURE.ID = INSERTED.ID end else if not update(DATECHANGED) begin set @DateChanged = GetDate() update dbo.PDACCOUNTSTRUCTURE set DATECHANGED = @DateChanged from dbo.PDACCOUNTSTRUCTURE inner join INSERTED on PDACCOUNTSTRUCTURE.ID = INSERTED.ID end --peform the audit if dbo.UFN_AUDITENABLED('PDACCOUNTSTRUCTURE') = 1 begin -- the audit key is used to associate the rows in the audit table with a single atomic deletion/modification -- we pre-fetch the audit date to make sure both rows contain exactly the same date value. set @AuditKey = NewID() set @AuditDate = GetDate() INSERT INTO dbo.PDACCOUNTSTRUCTUREAUDIT( AUDITRECORDID, AUDITKEY, AUDITCHANGEAGENTID, AUDITDATE, AUDITTYPECODE, [DESCRIPTION], [SEQUENCE], [LENGTH], [PDACCOUNTTABLESAVAILABLEFORSEGMENTID], [SEPARATORCODE], [SEGMENTTYPE], [SITEID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [PDACCOUNTSYSTEMID], [ISPROJECTSEGMENT], [ELEMENTDEFINITIONCODE], [SEGMENTSEQUENCE], [SEGMENTCOLUMN], [ELEMENTTYPECODE], [CLOSINGELEMENT], [DEFAULTDESCRIPTIONSEQUENCE], [DEFAULTDESCRIPTIONLENGTH], [ISBASICGL], [ISCONSTANTVALUE] ) SELECT ID, @AuditKey, COALESCE(@ChangeAgentID,(SELECT CHANGEDBYID FROM INSERTED WHERE INSERTED.ID=DELETED.ID)), --If explicitly updating CHANGEDBYID, use that, else use the the one default one fetched above @AuditDate, 0, --Before Update [DESCRIPTION], [SEQUENCE], [LENGTH], [PDACCOUNTTABLESAVAILABLEFORSEGMENTID], [SEPARATORCODE], [SEGMENTTYPE], [SITEID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [PDACCOUNTSYSTEMID], [ISPROJECTSEGMENT], [ELEMENTDEFINITIONCODE], [SEGMENTSEQUENCE], [SEGMENTCOLUMN], [ELEMENTTYPECODE], [CLOSINGELEMENT], [DEFAULTDESCRIPTIONSEQUENCE], [DEFAULTDESCRIPTIONLENGTH], [ISBASICGL], [ISCONSTANTVALUE] FROM DELETED INSERT INTO dbo.PDACCOUNTSTRUCTUREAUDIT( AUDITRECORDID, AUDITKEY, AUDITCHANGEAGENTID, AUDITDATE, AUDITTYPECODE, [DESCRIPTION], [SEQUENCE], [LENGTH], [PDACCOUNTTABLESAVAILABLEFORSEGMENTID], [SEPARATORCODE], [SEGMENTTYPE], [SITEID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [PDACCOUNTSYSTEMID], [ISPROJECTSEGMENT], [ELEMENTDEFINITIONCODE], [SEGMENTSEQUENCE], [SEGMENTCOLUMN], [ELEMENTTYPECODE], [CLOSINGELEMENT], [DEFAULTDESCRIPTIONSEQUENCE], [DEFAULTDESCRIPTIONLENGTH], [ISBASICGL], [ISCONSTANTVALUE] ) SELECT ID, @AuditKey, COALESCE(@ChangeAgentID,CHANGEDBYID), --If explicitly updating CHANGEDBYID, use that, else use the the one default one fetched above @AuditDate, 1, --After Update [DESCRIPTION], [SEQUENCE], [LENGTH], [PDACCOUNTTABLESAVAILABLEFORSEGMENTID], [SEPARATORCODE], [SEGMENTTYPE], [SITEID], [ADDEDBYID], coalesce(@ChangeAgentID, [CHANGEDBYID]), [DATEADDED], coalesce(@DateChanged, [DATECHANGED]), [PDACCOUNTSYSTEMID], [ISPROJECTSEGMENT], [ELEMENTDEFINITIONCODE], [SEGMENTSEQUENCE], [SEGMENTCOLUMN], [ELEMENTTYPECODE], [CLOSINGELEMENT], [DEFAULTDESCRIPTIONSEQUENCE], [DEFAULTDESCRIPTIONLENGTH], [ISBASICGL], [ISCONSTANTVALUE] FROM INSERTED end |