Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: Triggers with DTS

    hi,
    a simple question, I have this table:
    -----------------------------
    CREATE TABLE [dbo].[A0_INSUMOS] (
    [ARTCODI] [int] NOT NULL ,
    [ARTNOMB] [varchar] (80) NULL ,
    [RUBCODI] [int] NULL ,
    [RUBNOMB] [varchar] (40) NULL ,
    [SRUBCODI] [int] NULL ,
    [SRUBNOMB] [varchar] (40) NULL ,
    [FAMCODI] [int] NULL ,
    [FAMNOMB] [varchar] (40) NULL ,
    [LINCODI] [int] NULL ,
    [LINNOMB] [varchar] (40) NULL ,
    [FABCOD] [int] NULL ,
    [FABNOMB] [varchar] (40) NULL ,
    [ARTTIP] [int] NULL ,
    [ARTTIPNOMB] [varchar] (40) NULL
    ) ON [PRIMARY]
    GO


    and this table have this trigger:
    -----------------------------
    CREATE TRIGGER [ACTUALIZA_DIMENSION] ON [dbo].[A0_INSUMOS]
    FOR INSERT, UPDATE
    AS
    declare @reg int
    declare @nuevorub int
    declare @viejorub int
    declare @rubro varchar(40)

    select @reg=artcodi from inserted
    select @nuevorub=rubcodi from inserted
    select @viejorub=rubcodi from deleted

    if ( @nuevorub <> @viejorub )
    begin
    select @rubro=rubnomb from a1_rubros where rubcodi = @nuevorub
    if @rubro is null
    BEGIN
    RAISERROR ('El rubro %d no existe',
    16, 1, @nuevorub)
    ROLLBACK TRANSACTION
    END
    update a0_insumos set rubnomb = @rubro where artcodi = @reg
    end

    -----------------------------


    when I insert o update a row, work fine, but when I run a dts, the trigger not run.

    Any idea?
    TIA
    Abel.

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Your DTS package is probably inserting several records in one transaction, which means this trigger only gets triggered once. It wouldn't get triggered for each row inserted.

    For example:

    insert into mytable
    select top 1000 * from myOthertable

    If there is a trigger on mytable, it only runs once. Your trigger needs to be set based instead of row based.

    Bill

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    hi and tanks for your reply.
    I make another test, for ex.

    update A0_INSUMOS
    set RUBCODI = 1

    but, the trigger not run, in the unique case when this run is when I insert o update a one row.
    why?????

    Thanks again.
    Abel.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    in an insert, there is no data in the DELETED table. So when you set "@viejorub=rubcodi from deleted", @viejorub ends up with a null value. Then your comparison "if @nuevorub <> @viejorub" yields false every time, because all comparison to a null value yield false, so your "select @rubro=rubnomb..." statement never runs.
    Follow cascred's advice and rewrite this as a set-based operation. Triggers should NEVER assume that all inserts will be single record.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by achiola
    hi and tanks for your reply.
    I make another test, for ex.

    update A0_INSUMOS
    set RUBCODI = 1

    but, the trigger not run, in the unique case when this run is when I insert o update a one row.
    why?????
    Actually I think you will find that your trigger does run. It is doing what you told it to do, and that is to update one row only, even if you insert 1000 rows.

    You have the wrong idea about a set based approach. In one trigger, you need to perform an update on a set of inserted rows. Also, you may want to reconsider your RAISERROR statement. If you insert 1000 rows, and only one row fails to meet your criteria, do you want all 1000 inserts to fail? Or do you want to allow 999 of the inserts to complete, and use a select statement to find the ones that did not?

    Here is an example (untested) of what I am talking about. No variables needed


    Code:
    CREATE TRIGGER [ACTUALIZA_DIMENSION] ON [dbo].[A0_INSUMOS]
    FOR INSERT, UPDATE
    AS
    
    BEGIN
    update a0
    set rubnomb = a1.rubnomb
    from inserted i
            join deleted d
                    on i.artcodi = d.artcodi
    	join a0_insumos a0
    		on a0.artcodi = i.artcodi
    	join a1.rubros a1
    		on i.rubcodi = a1.rubcodi
    where d.rubcodi <> i.rubcodi
    END
    Bill
    Last edited by cascred; 10-05-05 at 09:38.

  6. #6
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Very thanks to all.
    The raison of this trigger is "fix" the bad program, this was old acucobol prg, your maker never read Codd or Date. And now we are migration all file to SQL Server.

    Now I run DTS for initial migration, but, not is the final use. This program insert/update one to one record, and the idea is a roll back of all transacción (1 or 1000 row) if this faild.

    cascred, I will test yor code.

    blindman, I test this code:
    if ( @nuevorub <> @viejorub ) or ( @viejorub is null )
    and the trigger not run, never, indiead, I test a raise error in all case, but not run.


    Thanks again.
    And very sorry by my english.
    Abel.

  7. #7
    Join Date
    Sep 2005
    Posts
    161
    Since I know more about your project, I'll offer another suggestion. Don't use triggers for data migration.

    Import your data into temporary tables. Then write SQL scripts to validate this data and populate your destination tables.

    Your English is much better than my Spanish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •