DELETE TRIGGER - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

DELETE TRIGGER

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2010-01-11 : 10:54:55

Guys,

I am having an issue with the 'AFTER DELETE' trigger where the it is capturing only the first ID of the deleted recorded as supposed to the 4 records which are actually being deleted by the
deleted statement.

ALTER TRIGGER [dbo].[TRG_D_FLATTABLE]
ON [dbo].[FLATTABLE]
AFTER DELETE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON

DECLARE @ID BIGINT
SELECT @ID = ID FROM DELETED

INSERT
INTO TRACK
(
PK_KEY,
DML_TYP,
TAB_NAME
)
values(
@ID,
'D',
'FLATTABLE'
)
END

DELETE FROM FLATTABLE WHERE EMPID = 8
-- (4 row(s) affected)

However when I query the TRACK table there is only entry for the 'DML_TYP' = 'D' even though 4 rows have been physically delete from the FLATTABLE. The only entry in TRACK table correspond minimum ID value
of all the 4 IDs that got deleted from FLATRACK table

Any suggestions and inputs would help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 10:59:00

[code]ALTER TRIGGER [dbo].[TRG_D_FLATTABLE]
ON [dbo].[FLATTABLE]
AFTER DELETE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
INSERT
INTO TRACK
(
PK_KEY,
DML_TYP,
TAB_NAME
)

SELECT ID,
'D',
'FLATTABLE'
FROM DELETED

END
[/code]

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 04:37:36

"when I query the TRACK table there is only entry for the 'DML_TYP' = 'D' even though 4 rows have been physically delete"

Important point to know (assuming you don't already) is that a trigger is called once-per-batch and NOT once-per-row.

So a DELETE statement that deletes 4 rows (or a millions rows :) ) will only call the trigger once.

The rows involved in the database change are stored in pseudo tables "inserted" and "deleted" - for an INSERT there will be nothing in "deleted", for a DELETE nothing in "inserted", and for an update the original data will be in "deleted" and the new data in "inserted".

Hence visakh16's solution to copy the IDs from the "deleted" table into your "TRACK" table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 04:40:17

quote:
Originally posted by Kristen

"when I query the TRACK table there is only entry for the 'DML_TYP' = 'D' even though 4 rows have been physically delete"

Important point to know (assuming you don't already) is that a trigger is called once-per-batch and NOT once-per-row.

So a DELETE statement that deletes 4 rows (or a millions rows :) ) will only call the trigger once.

The rows involved in the database change are stored in pseudo tables "inserted" and "deleted" - for an INSERT there will be nothing in "deleted", for a DELETE nothing in "inserted", and for an update the original data will be in "deleted" and the new data in "inserted".

Hence visakh16's solution to copy the IDs from the "deleted" table into your "TRACK" table.

Good explanation
Nice and clear!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources