Delete Trigger

Question

Delete Trigger

lbrady

SSC Eights!

Points: 933
More actions
October 14, 2010 at 12:43 pm

#239821

Hi,
We are having an issue with some process deleting records from a table (SS 2005). I need to put a trigger in place that will delete the data from the original table, save the record into a new table and include the user who deleted the record.
Any help would be appreciated.
TIA

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Post the definitions of the tables please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lbrady SSC Eights! Points: 933 More actions · Answer 2

Source table: dbo.[HCS_STG_CNSMR_APLCTN]

[STG_DATA_PRCSS_TMSTMP] [datetime] NULL,

[STG_DATA_ROW_ID] [int] NOT NULL,

[CNSMR_APLCTN_CD] [char](3) NULL,

[PRCSS_STATUS_CD] [char](3)

Target table:

[dbo].[deleted_HCS_STG_CNSMR_APLCTN]

[STG_DATA_PRCSS_TMSTMP] [datetime] NULL,

[STG_DATA_ROW_ID] [int] NOT NULL,

[CNSMR_APLCTN_CD] [char](3) NULL,

[PRCSS_STATUS_CD] [char](3),

[USERID]varchar(8)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Should the user column be set to the login name of the user who deleted the rows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lbrady SSC Eights! Points: 933 More actions · Answer 4

lbrady

SSC Eights!

Points: 933

October 14, 2010 at 1:05 pm

#1237154

yes, that is correct.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Edit: nevermind. I'm not thinking

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

This should do the job. Note that I've increased the size of the userID column and added a datedeleted. Doesn't help much if you know who deleted rows but not when they were deleted.

CREATE TABLE [dbo].[deleted_HCS_STG_CNSMR_APLCTN] (

[STG_DATA_PRCSS_TMSTMP] [datetime] NULL,

[STG_DATA_ROW_ID] [int] NOT NULL,

[CNSMR_APLCTN_CD] [char](3) NULL,

[PRCSS_STATUS_CD] [char](3),

[USERID] varchar(255),

[DateDeleted] DATETIME DEFAULT GETDATE()

)

GO

CREATE TRIGGER AuditDeletedRows ON dbo.[HCS_STG_CNSMR_APLCTN]

AFTER DELETE

AS

INSERT INTO [dbo].[deleted_HCS_STG_CNSMR_APLCTN] (STG_DATA_PRCSS_TMSTMP, STG_DATA_ROW_ID, CNSMR_APLCTN_CD, PRCSS_STATUS_CD, USERID)

SELECT STG_DATA_PRCSS_TMSTMP, STG_DATA_ROW_ID, CNSMR_APLCTN_CD, PRCSS_STATUS_CD, ORIGINAL_LOGIN() FROM deleted

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lbrady SSC Eights! Points: 933 More actions · Answer 7

lbrady

SSC Eights!

Points: 933

October 14, 2010 at 1:43 pm

#1237184

You are the KING! Thank you!