January 11, 2010 at 8:54 am
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
January 11, 2010 at 9:03 am
am-244616 (1/11/2010)
DECLARE @ID BIGINTSELECT @ID = ID FROM DELETED
Well, your code asks for only one value from the deleted table (which will have 4 rows if 4 rows were deleted), which it then inserts into the tracking table, so what did you expect to happen?
No need for variables at all.
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
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
January 11, 2010 at 9:32 am
If I'm not mistaken, Oracle runs triggers once per row in the transaction. MS-SQL runs the trigger once for the whole transaction. Your trigger is more Oracle style, while Gail's is MS style.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2010 at 9:43 am
Yeah it will be better if you modify the code what gail has suggested or you need to delete the rows one-by-one.
January 11, 2010 at 11:04 am
Thanks Gila that worked
January 11, 2010 at 12:35 pm
GSquared (1/11/2010)
If I'm not mistaken, Oracle runs triggers once per row in the transaction.
Depends. Oracle has per-row and per-statement triggers. I have no idea which is the default (if there is a default)
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply