Access Violations

Hi , I've noticed this behavior and I think I pinpointed the reason. But I
cannot understand why this is happening. I would really appreciate if
someone could shed some light on this for me.
We have a table that has instead of insert trigger on it. Whenever someone
is running an insert with order by the query fails with AV. If the trigger
is disabled the query executes fine. Here is the script below to recreate
the problem. We are running sql2k sp4 on win2003
/********************************************************/
-- create table
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[refGeoID] [int] NOT NULL ,
[Action] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] )
ON [PRIMARY]
GO
-- create trigger
create TRIGGER test_I_Trig ON dbo.test
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
DELETE FROM dbo.test
From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
INSERT INTO dbo.test (refGeoID, [Action])
SELECT i.refGeoID, i.[Action]
FROM inserted i
set nocount off
end
-- insert data
insert into test (refGeoID, [Action])
select 1,'I'
union all
select 2,'I'
union all
select 3,'I'
union all
select 4,'I'
union all
select 5,'I'
union all
select 6,'I'
union all
select 7,'I'
union all
select 8,'I'
union all
select 9,'I'
union all
select 10,'I'
union all
select 11,'I'
union all
select 12,'I'
union all
select 13,'I'
union all
select 14,'I'
union all
select 15,'I'
union all
select 16,'I'
union all
select 17,'I'
union all
select 18,'I'
union all
select 19,'I'
union all
select 20,'I'
-- try to run an insert statement - it will fail
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
-- now run the same query without order by - it works
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
-- now drop the trigger and run the insert with order by - it works
drop TRIGGER test_I_Trig
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
thank youHi
If this is an insert trigger then your identity value will not be in the
inserted table, therefore you are not going to achieve what you think you
want to do!
This may be similar to something like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272418
John
"Veronika" wrote:
> Hi , I've noticed this behavior and I think I pinpointed the reason. But I
> cannot understand why this is happening. I would really appreciate if
> someone could shed some light on this for me.
> We have a table that has instead of insert trigger on it. Whenever someone
> is running an insert with order by the query fails with AV. If the trigger
> is disabled the query executes fine. Here is the script below to recreate
> the problem. We are running sql2k sp4 on win2003
> /********************************************************/
> -- create table
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [refGeoID] [int] NOT NULL ,
> [Action] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [ID] )
> ON [PRIMARY]
> GO
>
> -- create trigger
> create TRIGGER test_I_Trig ON dbo.test
> INSTEAD OF INSERT
> AS
> BEGIN
> SET NOCOUNT ON
> DELETE FROM dbo.test
> From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT i.refGeoID, i.[Action]
> FROM inserted i
> set nocount off
> end
> -- insert data
> insert into test (refGeoID, [Action])
> select 1,'I'
> union all
> select 2,'I'
> union all
> select 3,'I'
> union all
> select 4,'I'
> union all
> select 5,'I'
> union all
> select 6,'I'
> union all
> select 7,'I'
> union all
> select 8,'I'
> union all
> select 9,'I'
> union all
> select 10,'I'
> union all
> select 11,'I'
> union all
> select 12,'I'
> union all
> select 13,'I'
> union all
> select 14,'I'
> union all
> select 15,'I'
> union all
> select 16,'I'
> union all
> select 17,'I'
> union all
> select 18,'I'
> union all
> select 19,'I'
> union all
> select 20,'I'
>
> -- try to run an insert statement - it will fail
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> -- now run the same query without order by - it works
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
> -- now drop the trigger and run the insert with order by - it works
> drop TRIGGER test_I_Trig
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> thank you
>|||Hi John, thank you for your answer.
However, it doesn't seem that it's my problem. I am not inserting the
identity value. Also, with the trigger enabled the insert query works as
long as I am not using the order by clause. The AV is only generated when I
have both instead of trigger and order by .
"John Bell" wrote:
> Hi
> If this is an insert trigger then your identity value will not be in the
> inserted table, therefore you are not going to achieve what you think you
> want to do!
> This may be similar to something like:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272418
> John
> "Veronika" wrote:
> > Hi , I've noticed this behavior and I think I pinpointed the reason. But I
> > cannot understand why this is happening. I would really appreciate if
> > someone could shed some light on this for me.
> >
> > We have a table that has instead of insert trigger on it. Whenever someone
> > is running an insert with order by the query fails with AV. If the trigger
> > is disabled the query executes fine. Here is the script below to recreate
> > the problem. We are running sql2k sp4 on win2003
> >
> > /********************************************************/
> >
> > -- create table
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [refGeoID] [int] NOT NULL ,
> > [Action] [char] (1) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> > CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> > (
> > [ID] )
> > ON [PRIMARY]
> > GO
> >
> >
> > -- create trigger
> > create TRIGGER test_I_Trig ON dbo.test
> > INSTEAD OF INSERT
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > DELETE FROM dbo.test
> > From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
> >
> > INSERT INTO dbo.test (refGeoID, [Action])
> > SELECT i.refGeoID, i.[Action]
> > FROM inserted i
> > set nocount off
> >
> > end
> >
> > -- insert data
> > insert into test (refGeoID, [Action])
> > select 1,'I'
> > union all
> > select 2,'I'
> > union all
> > select 3,'I'
> > union all
> > select 4,'I'
> > union all
> > select 5,'I'
> > union all
> > select 6,'I'
> > union all
> > select 7,'I'
> > union all
> > select 8,'I'
> > union all
> > select 9,'I'
> > union all
> > select 10,'I'
> > union all
> > select 11,'I'
> > union all
> > select 12,'I'
> > union all
> > select 13,'I'
> > union all
> > select 14,'I'
> > union all
> > select 15,'I'
> > union all
> > select 16,'I'
> > union all
> > select 17,'I'
> > union all
> > select 18,'I'
> > union all
> > select 19,'I'
> > union all
> > select 20,'I'
> >
> >
> > -- try to run an insert statement - it will fail
> >
> > INSERT INTO dbo.test (refGeoID, [Action])
> > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> >
> > -- now run the same query without order by - it works
> > INSERT INTO dbo.test (refGeoID, [Action])
> > SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
> >
> > -- now drop the trigger and run the insert with order by - it works
> > drop TRIGGER test_I_Trig
> > INSERT INTO dbo.test (refGeoID, [Action])
> > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> >
> > thank you
> >|||Apologies if this is posted twice!!
Hi
The AV is an issue as you should be presented with a sensible error message,
but your design is flawed. With no id values the delete statement will never
be satisfied, therefore making it redundant. Removing the delete statement
from the trigger still gives the AV, but it makes the trigger redundant as
the insert can be carried out directly.
You will need to redesign the process, possibly using a temporary table or a
dummy column such as:
-- create table
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY NOT NULL ,
[refGeoID] [int] NOT NULL ,
[Action] [char] (1) NULL,
[Oldid] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] )
ON [PRIMARY]
GO
-- create trigger
create TRIGGER test_I_Trig ON dbo.test
INSTEAD OF INSERT
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
-- SELECT * FROM INSERTED
INSERT INTO dbo.test (refGeoID, [Action])
SELECT i.refGeoID, i.[Action]
FROM inserted i
order by i.oldid asc
DELETE FROM t
FROM dbo.test t
JOIN INSERTED i ON t.id = i.oldid
end
-- insert data
insert into test (refGeoID, [Action])
select 1,'I'
union all
select 2,'I'
union all
select 3,'I'
union all
select 4,'I'
union all
select 5,'I'
union all
select 6,'I'
union all
select 7,'I'
union all
select 8,'I'
union all
select 9,'I'
union all
select 10,'I'
union all
select 11,'I'
union all
select 12,'I'
union all
select 13,'I'
union all
select 14,'I'
union all
select 15,'I'
union all
select 16,'I'
union all
select 17,'I'
union all
select 18,'I'
union all
select 19,'I'
union all
select 20,'I'
-- try to run an insert statement - it will fail
SELECT * from test (nolock) order by id desc
ID refGeoID Action Oldid
-- -- -- --
20 20 I NULL
19 19 I NULL
18 18 I NULL
17 17 I NULL
16 16 I NULL
15 15 I NULL
14 14 I NULL
13 13 I NULL
12 12 I NULL
11 11 I NULL
10 10 I NULL
9 9 I NULL
8 8 I NULL
7 7 I NULL
6 6 I NULL
5 5 I NULL
4 4 I NULL
3 3 I NULL
2 2 I NULL
1 1 I NULL
(20 row(s) affected)
INSERT INTO dbo.test ( refGeoID, [Action], oldid)
SELECT top 10 refGeoID, [Action], id from test (nolock)
order by id desc
SELECT * from test (nolock) order by id desc
ID refGeoID Action Oldid
-- -- -- --
30 20 I NULL
29 19 I NULL
28 18 I NULL
27 17 I NULL
26 16 I NULL
25 15 I NULL
24 14 I NULL
23 13 I NULL
22 12 I NULL
21 11 I NULL
10 10 I NULL
9 9 I NULL
8 8 I NULL
7 7 I NULL
6 6 I NULL
5 5 I NULL
4 4 I NULL
3 3 I NULL
2 2 I NULL
1 1 I NULL
(20 row(s) affected)
John
"Veronika" wrote:
> Hi John, thank you for your answer.
> However, it doesn't seem that it's my problem. I am not inserting the
> identity value. Also, with the trigger enabled the insert query works as
> long as I am not using the order by clause. The AV is only generated when I
> have both instead of trigger and order by .
> "John Bell" wrote:
> > Hi
> >
> > If this is an insert trigger then your identity value will not be in the
> > inserted table, therefore you are not going to achieve what you think you
> > want to do!
> >
> > This may be similar to something like:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;272418
> >
> > John
> >
> > "Veronika" wrote:
> >
> > > Hi , I've noticed this behavior and I think I pinpointed the reason. But I
> > > cannot understand why this is happening. I would really appreciate if
> > > someone could shed some light on this for me.
> > >
> > > We have a table that has instead of insert trigger on it. Whenever someone
> > > is running an insert with order by the query fails with AV. If the trigger
> > > is disabled the query executes fine. Here is the script below to recreate
> > > the problem. We are running sql2k sp4 on win2003
> > >
> > > /********************************************************/
> > >
> > > -- create table
> > > CREATE TABLE [dbo].[test] (
> > > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > > [refGeoID] [int] NOT NULL ,
> > > [Action] [char] (1) NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> > > CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> > > (
> > > [ID] )
> > > ON [PRIMARY]
> > > GO
> > >
> > >
> > > -- create trigger
> > > create TRIGGER test_I_Trig ON dbo.test
> > > INSTEAD OF INSERT
> > > AS
> > > BEGIN
> > > SET NOCOUNT ON
> > >
> > > DELETE FROM dbo.test
> > > From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
> > >
> > > INSERT INTO dbo.test (refGeoID, [Action])
> > > SELECT i.refGeoID, i.[Action]
> > > FROM inserted i
> > > set nocount off
> > >
> > > end
> > >
> > > -- insert data
> > > insert into test (refGeoID, [Action])
> > > select 1,'I'
> > > union all
> > > select 2,'I'
> > > union all
> > > select 3,'I'
> > > union all
> > > select 4,'I'
> > > union all
> > > select 5,'I'
> > > union all
> > > select 6,'I'
> > > union all
> > > select 7,'I'
> > > union all
> > > select 8,'I'
> > > union all
> > > select 9,'I'
> > > union all
> > > select 10,'I'
> > > union all
> > > select 11,'I'
> > > union all
> > > select 12,'I'
> > > union all
> > > select 13,'I'
> > > union all
> > > select 14,'I'
> > > union all
> > > select 15,'I'
> > > union all
> > > select 16,'I'
> > > union all
> > > select 17,'I'
> > > union all
> > > select 18,'I'
> > > union all
> > > select 19,'I'
> > > union all
> > > select 20,'I'
> > >
> > >
> > > -- try to run an insert statement - it will fail
> > >
> > > INSERT INTO dbo.test (refGeoID, [Action])
> > > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> > >
> > > -- now run the same query without order by - it works
> > > INSERT INTO dbo.test (refGeoID, [Action])
> > > SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
> > >
> > > -- now drop the trigger and run the insert with order by - it works
> > > drop TRIGGER test_I_Trig
> > > INSERT INTO dbo.test (refGeoID, [Action])
> > > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> > >
> > > thank you
> > >|||We will review the design.
thank you
"John Bell" wrote:
> Apologies if this is posted twice!!
> Hi
> The AV is an issue as you should be presented with a sensible error message,
> but your design is flawed. With no id values the delete statement will never
> be satisfied, therefore making it redundant. Removing the delete statement
> from the trigger still gives the AV, but it makes the trigger redundant as
> the insert can be carried out directly.
> You will need to redesign the process, possibly using a temporary table or a
> dummy column such as:
> -- create table
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY NOT NULL ,
> [refGeoID] [int] NOT NULL ,
> [Action] [char] (1) NULL,
> [Oldid] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [ID] )
> ON [PRIMARY]
> GO
> -- create trigger
> create TRIGGER test_I_Trig ON dbo.test
> INSTEAD OF INSERT
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> -- SELECT * FROM INSERTED
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT i.refGeoID, i.[Action]
> FROM inserted i
> order by i.oldid asc
> DELETE FROM t
> FROM dbo.test t
> JOIN INSERTED i ON t.id = i.oldid
> end
> -- insert data
> insert into test (refGeoID, [Action])
> select 1,'I'
> union all
> select 2,'I'
> union all
> select 3,'I'
> union all
> select 4,'I'
> union all
> select 5,'I'
> union all
> select 6,'I'
> union all
> select 7,'I'
> union all
> select 8,'I'
> union all
> select 9,'I'
> union all
> select 10,'I'
> union all
> select 11,'I'
> union all
> select 12,'I'
> union all
> select 13,'I'
> union all
> select 14,'I'
> union all
> select 15,'I'
> union all
> select 16,'I'
> union all
> select 17,'I'
> union all
> select 18,'I'
> union all
> select 19,'I'
> union all
> select 20,'I'
>
> -- try to run an insert statement - it will fail
> SELECT * from test (nolock) order by id desc
> ID refGeoID Action Oldid
> -- -- -- --
> 20 20 I NULL
> 19 19 I NULL
> 18 18 I NULL
> 17 17 I NULL
> 16 16 I NULL
> 15 15 I NULL
> 14 14 I NULL
> 13 13 I NULL
> 12 12 I NULL
> 11 11 I NULL
> 10 10 I NULL
> 9 9 I NULL
> 8 8 I NULL
> 7 7 I NULL
> 6 6 I NULL
> 5 5 I NULL
> 4 4 I NULL
> 3 3 I NULL
> 2 2 I NULL
> 1 1 I NULL
> (20 row(s) affected)
>
> INSERT INTO dbo.test ( refGeoID, [Action], oldid)
> SELECT top 10 refGeoID, [Action], id from test (nolock)
> order by id desc
> SELECT * from test (nolock) order by id desc
> ID refGeoID Action Oldid
> -- -- -- --
> 30 20 I NULL
> 29 19 I NULL
> 28 18 I NULL
> 27 17 I NULL
> 26 16 I NULL
> 25 15 I NULL
> 24 14 I NULL
> 23 13 I NULL
> 22 12 I NULL
> 21 11 I NULL
> 10 10 I NULL
> 9 9 I NULL
> 8 8 I NULL
> 7 7 I NULL
> 6 6 I NULL
> 5 5 I NULL
> 4 4 I NULL
> 3 3 I NULL
> 2 2 I NULL
> 1 1 I NULL
> (20 row(s) affected)
> John
>
> "Veronika" wrote:
> > Hi John, thank you for your answer.
> > However, it doesn't seem that it's my problem. I am not inserting the
> > identity value. Also, with the trigger enabled the insert query works as
> > long as I am not using the order by clause. The AV is only generated when I
> > have both instead of trigger and order by .
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If this is an insert trigger then your identity value will not be in the
> > > inserted table, therefore you are not going to achieve what you think you
> > > want to do!
> > >
> > > This may be similar to something like:
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;272418
> > >
> > > John
> > >
> > > "Veronika" wrote:
> > >
> > > > Hi , I've noticed this behavior and I think I pinpointed the reason. But I
> > > > cannot understand why this is happening. I would really appreciate if
> > > > someone could shed some light on this for me.
> > > >
> > > > We have a table that has instead of insert trigger on it. Whenever someone
> > > > is running an insert with order by the query fails with AV. If the trigger
> > > > is disabled the query executes fine. Here is the script below to recreate
> > > > the problem. We are running sql2k sp4 on win2003
> > > >
> > > > /********************************************************/
> > > >
> > > > -- create table
> > > > CREATE TABLE [dbo].[test] (
> > > > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > [refGeoID] [int] NOT NULL ,
> > > > [Action] [char] (1) NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> > > > (
> > > > [ID] )
> > > > ON [PRIMARY]
> > > > GO
> > > >
> > > >
> > > > -- create trigger
> > > > create TRIGGER test_I_Trig ON dbo.test
> > > > INSTEAD OF INSERT
> > > > AS
> > > > BEGIN
> > > > SET NOCOUNT ON
> > > >
> > > > DELETE FROM dbo.test
> > > > From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
> > > >
> > > > INSERT INTO dbo.test (refGeoID, [Action])
> > > > SELECT i.refGeoID, i.[Action]
> > > > FROM inserted i
> > > > set nocount off
> > > >
> > > > end
> > > >
> > > > -- insert data
> > > > insert into test (refGeoID, [Action])
> > > > select 1,'I'
> > > > union all
> > > > select 2,'I'
> > > > union all
> > > > select 3,'I'
> > > > union all
> > > > select 4,'I'
> > > > union all
> > > > select 5,'I'
> > > > union all
> > > > select 6,'I'
> > > > union all
> > > > select 7,'I'
> > > > union all
> > > > select 8,'I'
> > > > union all
> > > > select 9,'I'
> > > > union all
> > > > select 10,'I'
> > > > union all
> > > > select 11,'I'
> > > > union all
> > > > select 12,'I'
> > > > union all
> > > > select 13,'I'
> > > > union all
> > > > select 14,'I'
> > > > union all
> > > > select 15,'I'
> > > > union all
> > > > select 16,'I'
> > > > union all
> > > > select 17,'I'
> > > > union all
> > > > select 18,'I'
> > > > union all
> > > > select 19,'I'
> > > > union all
> > > > select 20,'I'
> > > >
> > > >
> > > > -- try to run an insert statement - it will fail
> > > >
> > > > INSERT INTO dbo.test (refGeoID, [Action])
> > > > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> > > >
> > > > -- now run the same query without order by - it works
> > > > INSERT INTO dbo.test (refGeoID, [Action])
> > > > SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
> > > >
> > > > -- now drop the trigger and run the insert with order by - it works
> > > > drop TRIGGER test_I_Trig
> > > > INSERT INTO dbo.test (refGeoID, [Action])
> > > > SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> > > >
> > > > thank you
> > > >