July 6, 2011 at 8:39 am
I'm apparently having a "stupid" day as I'm trying to archive a deleted record from a table into an archive table. The trigger code is:/****** Object: Trigger [dbo].[del_tAddresses] Script Date: 07/06/2011 09:22:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[del_tAddresses] ON [dbo].[tAddresses]
FOR DELETE
AS
--Archive the old record
INSERT INTO arcAddresses
([lngAddressID],
[lngPersonID],
[intAddressTypeID],
[strAddress],
[strAddress2],
[strCity],
[strState],
[strCountry],
[strPostCode],
[dtBadAddress],
[strBadAddressReason],
[blnPreferred],
[dtEdited],
[strWhoEdited])
SELECT del.[lngAddressID],
del.[lngPersonID],
del.[intAddressTypeID],
del.[strAddress],
del.[strAddress2],
del.[strCity],
del.[strState],
del.[strCountry],
del.[strPostCode],
del.[dtBadAddress],
del.[strBadAddressReason],
del.[blnPreferred],
del.[dtEdited],
del.[strWhoEdited]
FROM deleted del
RAISERROR ('Trigger should have executed!',16,1)
I do get the raiserror message but no record is being inserted in the archive table arcAddresses. I have a very similar trigger running on UPDATE and it works as it should, inserting the address record prior to modification in the archive table. Note that only under special circumstances should this ever process more than one record at a time. Any clues as to what I've missed would be much appreciated.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 8:40 am
change your raiserror to a print statement...wouldn't raising an error of 16 or above inside the trigger rollback the trigger?
Lowell
July 6, 2011 at 8:48 am
The trigger's fine, just RAISERROR with a severity of 16 is used for errors. Informational messages are severity 10 or lower.
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
July 6, 2011 at 8:59 am
Unfortunately, the RAISERROR was added just to verify that the trigger was actually executing. The 16 does prevent the delete from actually happening, but when I take that statement out, the INSERT doesn't seem to work, as no record is added to the archive table.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 9:10 am
As Lowell said, replace it with a print. If the raiserror rolls back the delete the trigger will roll back too.
There is nothing I can see wrong with your trigger. Are you sure the delete does delete rows? Sure there are no real errors raised? No triggers on arcAddresses that do silly things?
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
July 6, 2011 at 10:53 am
The RAISERROR doesn't rollback the transaction, you would need an explicit ROLLBACK TRANSACTION after that statement.
Wendel, your trigger is fine, but I'm thinking other triggers may be interferring. Disable all the rest and then delete like so:
alter table tAddresses disable trigger all;
alter table tAddresses enable trigger del_tAddresses;
delete from tAddresses where ...
EDIT: typo
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 6, 2011 at 10:59 am
The mystery persists - if I replace the RAISERROR statement with a Print statement, no message is displayed. If I reduce the severity level of the RAISERROR statement to 10 or less, no message is displayed. In both cases the record is deleted, but no record is added to the archive table. If I leave it at 16, the error message is displayed, and the delete is stopped, even though there is no RollBack statement. Further background - this is SQL Server 2008 R2 Standard version, and I'm working in a test database at the moment. Also, the trigger is shown as enabled in Mgmt Studio. It appears that somehow the INSERT statement just isn't working, or else the Deleted table is empty when the trigger executes.
I should add that I am deleting a record in a query that is being run in Mgmt Studio, and the record does get deleted as rerunning the query (that is being sorted in date/time order) no longer returns the record. Also there are no errors being recorded in the SQL Server log file, on in the system or application logs.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 11:00 am
toddasd (7/6/2011)
The RAISERROR doesn't rollback the transaction, you would need an explicit ROLLBACK TRANSACTION after that statement.Wendel, your trigger is fine, but I'm thinking other triggers may be interferring. Disable all the rest and then delete like so:
alter table tAddresses disable trigger all;
alter table tAddresses enable trigger del_tAddresses;
delete from tAddresses where ...
EDIT: typo
I don't believe that is correct.
A trigger is an implicit transaction, meaning any error causes it to rollback..which would of course rollback the DELETE operation that initiated the call of the trigger itself.
it's trivial to test that for yourself, but let me know and i'll post an example query.
Lowell
July 6, 2011 at 11:14 am
i stand corrected; unless the call was wrapped in a transaction, the code does execute, the row gets deleted, and the archive table does get the deleted row even with the RAISERROR call inside the trigger.
here's the code i used to test:
CREATE TABLE [tAddresses] (
[lngAddressID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[lngPersonID] BIGINT,
[intAddressTypeID] INT,
[strAddress] VARCHAR(30),
[strAddress2] VARCHAR(30),
[strCity] VARCHAR(30),
[strState] VARCHAR(30),
[strCountry] VARCHAR(30),
[strPostCode] VARCHAR(10),
[dtBadAddress] DATETIME,
[strBadAddressReason] VARCHAR(30),
[blnPreferred] BIT,
[dtEdited] DATETIME,
[strWhoEdited] VARCHAR(30))
--the archive table
CREATE TABLE [arcAddresses] (
[lngAddressID] BIGINT,
[lngPersonID] BIGINT,
[intAddressTypeID] INT,
[strAddress] VARCHAR(30),
[strAddress2] VARCHAR(30),
[strCity] VARCHAR(30),
[strState] VARCHAR(30),
[strCountry] VARCHAR(30),
[strPostCode] VARCHAR(10),
[dtBadAddress] DATETIME,
[strBadAddressReason] VARCHAR(30),
[blnPreferred] BIT,
[dtEdited] DATETIME,
[strWhoEdited] VARCHAR(30))
GO
CREATE TRIGGER [dbo].[del_tAddresses] ON [dbo].[tAddresses]
FOR DELETE
AS
BEGIN
--Archive the old record
INSERT INTO arcAddresses
([lngAddressID],
[lngPersonID],
[intAddressTypeID],
[strAddress],
[strAddress2],
[strCity],
[strState],
[strCountry],
[strPostCode],
[dtBadAddress],
[strBadAddressReason],
[blnPreferred],
[dtEdited],
[strWhoEdited])
SELECT del.[lngAddressID],
del.[lngPersonID],
del.[intAddressTypeID],
del.[strAddress],
del.[strAddress2],
del.[strCity],
del.[strState],
del.[strCountry],
del.[strPostCode],
del.[dtBadAddress],
del.[strBadAddressReason],
del.[blnPreferred],
del.[dtEdited],
del.[strWhoEdited]
FROM deleted del
RAISERROR ('Trigger should have executed!',16,1)
--PRINT 'Trigger should have executed!'
END --TRIGGER
GO
--sample data
INSERT INTO tAddresses([strAddress],[strAddress2],[strCity],[strState],[strCountry],[strPostCode])
SELECT '123 MAIN ST', 'SUITE 100 BUILDING 12','Miami','FL','USA','33816' UNION ALL
SELECT '456 MAIN ST', 'SUITE 200 BUILDING 12','Miami','FL','USA','33816' UNION ALL
SELECT '789 MAIN ST', 'SUITE 300 BUILDING 12','Miami','FL','USA','33816'
GO
DELETE FROM tAddresses WHERE [strAddress] = '123 MAIN ST'
SELECT * FROM tAddresses
SELECT * FROM arcAddresses
Lowell
July 6, 2011 at 11:31 am
There are no other triggers on either table? Absolutely sure of that?
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
July 6, 2011 at 11:54 am
GilaMonster (7/6/2011)
There are no other triggers on either table? Absolutely sure of that?
Exactly. There has to be an INSTEAD OF INSERT trigger on the archive table. Lowell and I have independantly replicated your tables and trigger and it works as expected.
EDIT: oops, INSTEAD OF INSERT on the archive table, or INSTEAD OF DELETE on the base table.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 6, 2011 at 11:59 am
Lowell - I am able to duplicate your results in a test database constructed specifically for that purpose. I noted that you had wrapped the trigger in and BEGIN and END so I tried that in the actual (but test) database and I get an error message, the record is not deleted, and no record is archived. Still a puzzle.
GilaMonster - there are no triggers on the archive table (arcAddresses). There is one other trigger that is a FOR UPDATE that looks much like the DELETE trigger, and works, on the table tAddresses. It archives the record from the Deleted table, and then updates the Inserted table with the current date/time and the SQL Server login.
I am now suspicious that I am fighting a security issue. Because of some problems that occurred with Active Directory during hardware upgrades a few months ago, I had to be assigned a new userID, and that UserID isn't showing up as a user of any of the databases except the ArchiveTest database that I created to duplicate Lowell's results. This could get ugly. Unfortunately I have to leave this for now and chase other issues. But I'll be back later.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 12:00 pm
toddasd (7/6/2011)
GilaMonster (7/6/2011)
There are no other triggers on either table? Absolutely sure of that?Exactly. There has to be an INSTEAD OF INSERT trigger on the archive table. Lowell and I have independantly replicated your tables and trigger and it works as expected.
EDIT: oops, INSTEAD OF INSERT on the archive table, or INSTEAD OF DELETE on the base table.
Or an AFTER INSERT on the archive table.
One of the most messed up things I've seen with triggers was an AFTER INSERT trigger on a table that inserted the rows into a different table and then deleted the just-inserted rows. No matter what was inserted the table was empty. Took quite some time to figure that one out.
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
July 6, 2011 at 12:02 pm
Shouldn't be a security issue. Those cause visible errors.
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
July 6, 2011 at 12:20 pm
One more test, Wendell. Can you insert directly into the archive table? No trigger, no proc, just run an insert into arcAddresses with static values in a query window.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply