Trigger err:Previous count = 0,current count = 1.

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger err:Previous count = 0,current count = 1.

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2013-09-16 : 10:27:40

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows:

for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
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 ((SELECT COUNT(*) FROM deleted) > 0 AND (SELECT COUNT(*) FROM deleted) < 2)
BEGIN
INSERT INTO [dbo].[tbl_Audit]
([SourceTable]
,[RecordPrimaryKey]
,[UserName]
,[HostName]
,[Action]
,[DateTime])
SELECT 'tbl_attendance'
,AttPatternId
,SUSER_SNAME()
,HOST_NAME()
,'DELETE'
,GETDATE()
FROM deleted d
commit transaction
END
IF @@ERROR!=0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error bulk deleting data in [tbl_attendance] from Table Triggier',11,1)
END

END

For single insert/update
ALTER TRIGGER [dbo].[TRG_Update_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER UPDATE
AS
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 inserted) > 0 )
BEGIN
INSERT INTO [dbo].[tbl_EY_Audit]
([SourceTable]
,[RecordPrimaryKey]
,[UserName]
,[HostName]
,[Action]
,[DateTime])
SELECT 'tbl_attendance'
,AttPatternId
,SUSER_SNAME()
,HOST_NAME()
,'UPDATE'
,GETDATE()
FROM inserted d
commit transaction
END

IF @@ERROR!=0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error updating data in [tbl_attendance] from Table Triggier',11,1)
END

END

When I try to run the website, the database error I am getting is:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 11:33:02

Change the first one to like shown below. The change is that I am commenting out your begin tran and end tran. Similar changes in the other one will be required. Also, if you are on SQL 2005 or later, use try catch rather than @@ERROR

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS 
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 ((SELECT COUNT(*) FROM deleted) > 0 AND (SELECT COUNT(*) FROM deleted) < 2)
BEGIN
INSERT INTO [dbo].[tbl_Audit]
([SourceTable]
,[RecordPrimaryKey]
,[UserName]
,[HostName]
,[Action]
,[DateTime])
SELECT 'tbl_attendance'
,AttPatternId
,SUSER_SNAME()
,HOST_NAME()
,'DELETE'
,GETDATE()
FROM deleted d
--commit transaction
END
IF @@ERROR!=0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error bulk deleting data in [tbl_attendance] from Table Triggier',11,1)
END

END

gagani
Posting Yak Master

112 Posts

Posted - 2013-09-16 : 12:06:43

Thank you for that. Could you please let me know the reason behind commenting begin/commit transaction

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 12:40:16

When you do any kind of DML operation - DELETE, for example - internally SQL Server opens a transaction for you. The begin transaction happens before any deletes, and the commit transaction happens after the delete and after any triggers have been fired.

Even though the transaction is opened as implicit, you can commit or rollback that transaction in the trigger.

So when you begin another transaction in the trigger, the tran count becomes two, but you have only one commit. That is the reason for the mismatch in the expected tran counts vs what SQL Server is seeing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources