SQL Server Performance

Trigger - INSERT/SELECT

Discussion in 'Preguntas sobre SQL Server en Español.' started by condeba92, Jul 12, 2007.

  1. condeba92 New Member

    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.
  2. FrankKalis Moderator

    Okay, not that I pretend to know Spanish, but I think I can guess what the question is about.

    A trigger fires once per statement. In your first example you're looping and the trigger fires @n times. Once for each single INSERT statement. In the second example you have a setbased operation with all rows inserted at once, so the trigger fires only once.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  3. condeba92 New Member

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

    Currently not much in SQL Server. I know Oracle has such a concept to fire a trigger on a row level. To the best of my knowledge I think that SQL Server triggers are still on the batch level. So, using the looping you ensure that the trigger is fired each time. In the INSERT...SELECT... version I don't think there is a way to do this.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  5. Luis Martin Moderator

    Lo lamento estoy muy ocupado.
    En este caso no tengo comentarios más que agradecer a Frank.

    Sorry I'm very busy.
    Thanks Frank.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  6. Adriaan New Member

    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.
  7. alzdba Member

    The only spanish I know is "una servesa porfavor" [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<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 />
  8. satya Moderator

    The reason I have locked that thread, it stands duplicate. As originator is bi-lingual it shouldn't be a problem to continue your discussion in English here.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />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) ...<br /><br />***<br />....<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  9. Adriaan New Member

    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.
  10. satya Moderator

    No match to Luis in Spanish section [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  11. condeba92 New Member

    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=':)' />]
  12. Adriaan New Member

    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.
  13. condeba92 New Member

    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.
  14. Adriaan New Member

    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.
  15. condeba92 New Member

    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
  16. Adriaan New Member

    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?
  17. condeba92 New Member

    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.
  18. shashikant New Member


    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 
  19. Adriaan New Member

    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.
  20. condeba92 New Member

    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
  21. condeba92 New Member

    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.



Share This Page