trigger with insert into .. select *
-
Is it possibel to use an insert trigger for a table where you are inserting a set of rows from another table
something like
create trigger inserted_aa
on aa
for insert as
insert into AA
select * from BB.
if this works will it check row by row insert or for the entire set of rows??
any help will be greatly appreciated.
TIA
-
I think it can be done, Just Haven't tried. But I would recommend to disable the NESTED Trigger option (which it is by default). Therefore the rows inserted in the trigger context should not fire the trigger itself. Ah if you decide to enable the nested triggers be ready to re architect your solution
only 32 levels supported according to BOL.will it check row by row insert or for the entire set of rows?? I don't think so, SQL works with Sets.
* Noel -
Why don't you try it yourself?
CREATE TABLE [AA] (
[A1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER InsertTest ON dbo.AA
FOR INSERT
AS
Insert into AA
SELECT B1 FROM BB, Inserted
--WHERE B1 = INSERTED.A1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [BB] (
[B1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO BB
VALUES('B1')
INSERT INTO BB
VALUES('B2')
INSERT INTO BB
VALUES('A2')
INSERT INTO AA
SELECT B1 from BB
I really hope you gave an example of such a trigger, and that you did not really have the result in mind, or else you'll be in for a shock...
Hth
Greetz,
Hans Brouwer -
>> I really hope you gave an example of such a trigger, and that you did not really have the result in mind, or else you'll be in for a shock...<<
CREATE TABLE [AA] (
[A1] [varchar] (4)
)
GO
CREATE TABLE [BB] (
[B1] [varchar] (4)
)
GO
CREATE TRIGGER InsertTest ON dbo.AA
FOR INSERT
AS
Insert into AA (A1)
SELECT B1 FROM BB
GO
INSERT INTO BB
VALUES('B1')
INSERT INTO BB
VALUES('B2')
INSERT INTO BB
VALUES('A2')
INSERT INTO AA
SELECT B1 from BB
select * from AA
RESULTS:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(3 row(s) affected)
A1
----
B1
B2
A2
B1
B2
A2
(6 row(s) affected)
Well, it worked as I SAID IT WILL, so what is the problem?
* Noel -
Actually the reply was ment for Ishaan99. Take a look at the results. For 1 run of the INSERT statement you get double entries exactkly the same. Imagine you run this statement several times. You'll get numerous double entries, that's why I asked whether this really was what he had in mind.
Greetz,
Hans Brouwer -
Hans,
I do Agree with you that it sounds like totally unreasonable design, I just tried to show that it is posible to do it. I don't know what is the real purpose of this and to be honest I probably would not want to hear it either because like I said can't figure out a possible scenario and probably with a good design you don't need it
Cheers,
* Noel
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply