Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gagani
Posting Yak Master
112 Posts |
Posted - 2013-08-30 : 07:30:05
|
The following is the trigger which create a row in the audit table when a single deletion is occurred.ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here BEGIN TRANSACTION IF ( (SELECT COUNT(*) FROM deleted) > 0 ) BEGIN INSERT INTO [dbo].[tbl_EY_Audit] ([SourceTable] ,[RecordPrimaryKey] ,[UserName] ,[HostName] ,[Action] ,[DateTime]) SELECT 'tbl_attendance' ,AttPatternId ,SUSER_SNAME() ,HOST_NAME() ,'DELETE' ,GETDATE() FROM deleted d END IF @@ERROR!=0 BEGIN ROLLBACK TRANSACTION RAISERROR('Error deleting data in [tbl_attendance] from Table Triggier',11,1) END COMMIT TRANSACTIONENDI am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletionALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here BEGIN TRANSACTION IF (SELECT COUNT(*) FROM deleted) > 1 BEGIN --SET @isBulkTried = 1 RAISERROR('YOU CAN ONLY DELETE ONE RECORD AT A TIME.',10,1) ROLLBACK TRANSACTION END IF @@ERROR!=0 BEGIN ROLLBACK TRANSACTION RAISERROR('Error bulk deleting data in [tbl_attendance] from Table Triggier',11,1) END END |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-30 : 08:17:35
|
You need an else clause to insert when there is one row. See in red below:ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]ON [dbo].[tbl_attendance]AFTER DELETEAS BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for trigger hereBEGIN TRANSACTIONIF (SELECT COUNT(*) FROM deleted) > 1BEGIN--SET @isBulkTried = 1RAISERROR('YOU CAN ONLY DELETE ONE RECORD AT A TIME.',10,1)ROLLBACK TRANSACTIONENDELSE INSERT INTO....IF @@ERROR!=0BEGINROLLBACK TRANSACTIONRAISERROR('Error bulk deleting data in [tbl_attendance] from Table Triggier',11,1)ENDEND |
|
|
|
|
|
|
|