Trigger - INSERT/SELECT

Hola a todos, tengo el siguiente problema:
Cuando hago:
while @i<=@n
begin
insert tabla1(campo1,campo2) values(valor1,valor2)
select @i=@i+1
end
el trigger de tabla1 se ejecuta 'n' veces, pero cuando hago:

insert tabla1
select * from tabla2

el trigger de tabla1 solo se ejecuta 1 vez y se insertan por supuesto las 'n' filas de tabla2 en tabla1.

Como puedo hacer para que en este INSERT ... SELECT se ejecute 'n' veces el trigger de la tabla1.

Gracias.

Exactly, FrankKalis.
So, what can i do to fired 'n' times a trigger in a set-based operations ?
Thank you.

I don't understand Spanish either - but I tried to add this on the other thread (which should NOT have been locked since we do not all understand Spanish) ...

***

If you're a programmer, rather than a database developer, you are probably used to move information around bit by bit, one thing at a time. However, in a database you can move values from table to table, from multiple columns, from multiple rows, all in one statement, and at the same time. This is called the set-based approach.

Do not use a VALUES list as the 'source' for the INSERT, instead use a SELECT statement based on the inserted and/or deleted snapshots, and any other relevant table.

Read BOL on CREATE TRIGGER.

The only spanish I know is "una servesa porfavor" [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Indeed, since we should prefer a set-based approach, if your system can handle the "large" transaction for :<br /><i>insert into xyz (...)<br />select ... from abc <br />order by (clustered index of xyz)</i><br /><br />The trigger will be fired once, but you'll have acces to the full inserted data using the "inserted" system-object.<br />and if you want to you can loop through it if needed using whatever means you want to.<br />

Yes, but it would be nice to hav3e this discussion in the English section. Especially since Luis mentions he doesn't have time to respond, and I'm not sure if there are many others responding to posts in the Spanish section.

Alzdba, you right, a beer is a good idea sometimes after work! <br />Ok guys, we can continue our conversation in english, but i'm still with this kind of problem.<br />FranKKalis talk with the truth. T-SQL can't handle this problem about triggers, but Oracle can do that.<br />I'm trying to imitate PL code behavior with triggers and other set-based complex code.<br />Well, i need to solve this problem anyway with T-SQL.<br />Anybody can help me with a good idea about it?<br />Thank you.[<img src='/community/emoticons/emotion-1.gif' alt='' />]

If you could just translate the exact problem into English.

Please explain why the set-based approach doesn't work for you. If you're absolutely sure it doesn't, then you might use a cursor based on the inserted and/or deleted snapshots.

Please dear friend, read the problem from the first post.
Here it's:
-------------------------------------------------------------------------
Dear friends, i need to know how i have to do to execute a trigger 'n' times when a execute a command line like this:

insert table1
select * from table2

I this kind of command line, the table1 trigger execute just one time, but insert 'n' rows in table1 table.

When i execute the following command, the table1 trigger executes 'n' times:
...
while @i<=@n
begin
insert table1(field1,field2) values(value1,value2)
select @i=@i+1
end
...
Thank's
-------------------------------------------------------------------------
Thank Adriaan.

A trigger will fire for each insert action. If the insert contains multiple rows, the trigger fires once. If you have 5 inserts in succession, the trigger fires five times.

Note that if you have an Access front-end that executes an INSERT into a SQL Server table, for multiple rows, using the Jet engine, then the Jet engine will insert the rows into the SQL Server table one at a time. In that case the trigger will fire for each row individually.

That's not really a problem, as long as you know what the client application does, and how to handle inserts of more than one row. In other words, always program with the set-based approach. Only program with the cursor-based approach if you have a quirky requirement that forces you to.

Ok, i think i can understand your point, but i'm working only with Sql query analizer, i have not front end like an Access program.
I have to do all i had explain with T-SQL in server machine.
I need to know how i have to do to execute a trigger 'n' times in set-based operation like this:
insert table1
select * from table2
Yes, i know FranKKalis say it is not posible in T-SQL.
For example, i have been thinking in 2 solutions about it:
(1)- Loop
while @i<=@n
begin
insert table1(field1,field2) values(value1,value2)
select @i=@i+1
end
(2)- Become trigger part of main code, i mean:
while @i<=@n
begin
(* trigger logical *)
insert table1(field1,field2) values(value1,value2)
select @i=@i+1
end
Thank you Adriaan.
Anybody think that this kind of problem can be resolve it in another way ?
Thank's

A trigger is part of the table definition. In SQL 2005, you can run an action query with some extra keywords, and then you have access to the 'inserted' and 'deleted' snapshots straight from your script or sproc.

Can you explain what you are inserting into the table? And what do you expect the trigger to do in response to the insert?

I just wanna fired my trigger 'n' times using a set-based operations like INSERT / SELECT.
I think a solution like 2 can work faster, but in other hand, i have more code to write. Well, i guest nothing it's free.
Thank you.

Hi to all
try this example and tell what u will notice regarding firing of trigger. ?



--Create 1st table and add trigger on that table
CREATE TABLE [Trigger1] (
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

Code:

 [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


---create 2nd table for inserting values into the 2nd table
CREATE TABLE [Trigger2] (
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


--Create Trigger on first table trigger 
CREATE Trigger TriggerTest on Trigger1  
for insert  
as  
insert into Trigger2   
Select * from inserted

------------------------------------------------------------
--after that insert values as follows in the 1st table . check records in the 2nd table whether they 
--are inserting into the 2nd table. then truncate the first table 
--and use the inert into trigger1 select * from trigger2 statement and check there r 30 records in the 
--trigger2 table .......
-- Name    Code   TableName
-- a11
-- b22
-- d44
-- f66
-- g77
-- j1010
-- c33
-- e55
-- h88
-- i99
--------------------------------------------------

--==============================
insert into trigger1
select * from trigger2
--==============================
select * from trigger1



--I hope this this might clear your doubts 

regards
shashi kant


Keep In Touch
Thanks & Regards 
Shashi Kant Chauhan 

Why does your trigger have to fire more than once? If you explain WHY, perhaps we might be able to explain how you could use the set-based approach.

If you HAVE to make it repeat, you could try this:

CREATE TRIGGER MyTrigger ON MyTable
FOR INSERT
AS

DECLARE @repeat INT, @runner INT

SET @repeat = @@ROWCOUNT
SET @runner = 1

WHILE @runner <= @repeat
BEGIN

........your trigger code..............

SET @runner = @runner + 1
END

GO

***

But you have to understand that if you're inserting 1 million rows into the table, then the code in the WHILE loop will repeat 1 million times. You also have to understand that while the trigger is executing, the insert does not get committed. You will probably run into locking and blocking issues.

Thank's guys!
shashikant, i will make some test about your code concept.
adriaan, i know what is all about when you make a loop this way, but maybe i'have not another way to do my work. WHY?: because i'm building code with a set-base approach, like PL, you know, but with T-SQL.
I will think about your code too.
Thank's

For example, i have been thinking in 2 solutions about it:
(1)- Loop
while @i<=@n
begin
insert table1(field1,field2) values(value1,value2)
select @i=@i+1
end
(2)- Become trigger part of main code, i mean:
while @i<=@n
begin
(* trigger logical *)
insert table1(field1,field2) values(value1,value2)
select @i=@i+1
end
(3)- Mix code (shashikant + adriaan code concept): Become set-based operations part of trigger, then do INSERT/SELECT command. When trigger fired (just one time), use inserted table to make set-based operations inside of trigger.
This way i'dont have loop command and i can keep the set-based code anywhere.
Thank, shashikant and adriaan.