April 8, 2010 at 1:16 am
If i am deleting 4 records from one sql table.
Can i get all the four records from Deleted table using After Delete Trigger on this table?
I have tried this but able to get only one record out of 4.
Kindly Provide hints or suggestion for this.
Regards,
Nilesh.
April 8, 2010 at 1:27 am
In your trigger , if you
'Select count(*) from deleted'
then you will get 4 back ( in this case).
How do you mean 'only getting one' , are you thinking that the trigger is fired once for each row ?
That does not happen, the trigger fires once per statement.
April 8, 2010 at 2:15 am
You can get all the 4 deleted records. As said by Dave, query the "Deleted" table to get your deleted records..
Here is sample test code
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
CREATE TABLE TEST (A INT, B INT )
GO
--TRUNCATE TABLE TEST
INSERT INTO TEST
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4
UNION ALL
SELECT 5,5
UNION ALL
SELECT 6,6
UNION ALL
SELECT 7,7
UNION ALL
SELECT 8,8
UNION ALL
SELECT 9,9
GO
CREATE TRIGGER TRG_DELETE_TEST
ON TEST
FOR DELETE
AS
BEGIN
SELECT * FROM DELETED
END
GO
SELECT * FROM TEST
GO
DELETE FROM TEST WHERE A < 5 AND B < 5
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
April 8, 2010 at 2:34 am
COldCoffee (4/8/2010)
You can get all the 4 deleted records. As said by Dave, query the "Deleted" table to get your deleted records..Here is sample test code
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
CREATE TABLE TEST (A INT, B INT )
GO
--TRUNCATE TABLE TEST
INSERT INTO TEST
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4
UNION ALL
SELECT 5,5
UNION ALL
SELECT 6,6
UNION ALL
SELECT 7,7
UNION ALL
SELECT 8,8
UNION ALL
SELECT 9,9
GO
CREATE TRIGGER TRG_DELETE_TEST
ON TEST
FOR DELETE
AS
BEGIN
SELECT * FROM DELETED
END
GO
SELECT * FROM TEST
GO
DELETE FROM TEST WHERE A < 5 AND B < 5
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
Here i am trying to insert Deleted records in Archive table.
I am able to get same number of rows as Deleted in Select Query,
But getting only one row in Archive Table.
Here is what i have tried so far.
CREATE TRIGGER [dbo].[TriggerTestforDelete]
ON [dbo].[TriggerTest]
FOR DELETE
AS
--DECLARE VARIABLES Having one to one Mapping with ProdSubAppLangDependent Table
DECLARE @ID int
DECLARE @Price money
SELECT @ID = d.ID, @Price = d.Price
FROM deleted d
INSERT INTO ArchivePrice
VALUES(@ID,@Price)
April 8, 2010 at 2:54 am
As i said, you are only accounting a single row...
Try this...
CREATE TRIGGER [dbo].[TriggerTestforDelete]
ON [dbo].[TriggerTest]
FOR DELETE
AS
INSERT INTO ArchivePrice(ID,Price)
SELECT d.ID, d.Price
FROM deleted d
April 8, 2010 at 2:56 am
I have tried one more approach
CREATE TRIGGER [dbo].[TriggerTestforDelete]
ON [dbo].[TriggerTest]
FOR DELETE
AS
DECLARE @ID int
DECLARE @Price money
DECLARE @OldPrice money
DECLARE @NewPrice money
DECLARE @RecordCounter Int
SELECT @RecordCounter = COUNT(*) FROM Deleted
WHILE @RecordCounter > 0
BEGIN
SELECT @ID = d.ID, @OldPrice = d.Price , @NewPrice = d.Price FROM deleted d
INSERT INTO ArchivePrice
VALUES(@ID,@OldPrice,@OldPrice)
SET @RecordCounter = @RecordCounter - 1
END
This is returning me same number of rows as in Deleted.
But the values are replicating for all the rows. 🙁
April 8, 2010 at 3:21 am
Dave Ballantyne (4/8/2010)
As i said, you are only accounting a single row...Try this...
CREATE TRIGGER [dbo].[TriggerTestforDelete]ON [dbo].[TriggerTest]
FOR DELETE
AS
INSERT INTO ArchivePrice(ID,Price)
SELECT d.ID, d.Price
FROM deleted d
Thanks For this.:-)
April 8, 2010 at 3:46 am
Here is what i have tried so far.
CREATE TRIGGER [dbo].[TriggerTestforDelete]
ON [dbo].[TriggerTest]
FOR DELETE
AS
--DECLARE VARIABLES Having one to one Mapping with ProdSubAppLangDependent Table
DECLARE @ID int
DECLARE @Price money
SELECT @ID = d.ID, @Price = d.Price
FROM deleted d
INSERT INTO ArchivePrice
VALUES(@ID,@Price)
This is the problem in your code buddy.. when u use "select" to populate a local variable from a table, as in select "local variable" = "value" from table , then this will populate the local variable with the last ever row in the result set... thats the reason u are getting only one row...
Use the method Dave gave... insert whole date directly into "Archive" Table from "Deleted"
Hope it helped....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply