asked on

Update Trigger Sql

We have a trigger that needs to fire on one single column when it is updated. The trigger doesn't fire at all...below is the code:
alter TRIGGER [dbo].[Ens_UpConversion]
ON [dbo].[ens_phyinv]
after UPDATE

AS BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

            declare @item nvarchar(30),
                        @rowpointer nvarchar(60),
                        @cnt decimal(18,6)

      if update(count_qty)
       begin

            declare cntstat cursor local static for

             select item, rowpointer,count_qty
             from inserted

             open cntstat

              Fetch next from cntstat into @item,@rowpointer,@cnt
              while @@fetch_status = 0
begin
                   if ((substring(@item,4,1) in ('1','3') and @cnt <> 0) and ((select us_length from ens_dimmain where item = @item) is not null and (select us_length from ens_dimmain where item = @item) <> 0))
                   begin
                          begin tran
                              update ensp
                                    set cal_count = @cnt * us_length / 12
                                    from ens_phyinv ensp
                                      inner join ens_dimmain d on d.item = ensp.item
                                    where ensp.rowpointer = @rowpointer
                                    and ensp.item = @item
                                    if @@error <> 0
                                      commit tran
                                    else
                                      rollback tran
                        end /*if substring*/
Fetch next from cntstat into @item,@rowpointer,@cnt
                  end /*cursor*/
                  close cntstat
                  deallocate cntstat

       end /*if update(count_qty)*/

END

ste5an

How have you confirmed that it is not firing? Is it disabled?

btw, why do you use an cursor?? This should be sufficient:

ALTER TRIGGER [dbo].[Ens_UpConversion] ON [dbo].[ens_phyinv]
    AFTER UPDATE
AS
    SET NOCOUNT ON;

    IF UPDATE(count_qty)
        BEGIN 
            WITH    R AS ( SELECT   I.item ,
                                    I.rowpointer ,
                                    I.count_qty
                           FROM     INSERTED I
                           WHERE    ( SUBSTRING(I.item, 4, 1) IN ( '1', '3' )
                                      AND I.count_qty <> 0
                                      AND ( SELECT  us_length
                                            FROM    ens_dimmain
                                            WHERE   item = @item
                                          ) IS NOT NULL
                                      AND ( SELECT  us_length
                                            FROM    ens_dimmain
                                            WHERE   item = @item
                                          ) <> 0
                                    )
                         )
                UPDATE  ensp
                SET     cal_count = R.count_qty * us_length / 12
                FROM    ens_phyinv ensp
                        INNER JOIN ens_dimmain d ON d.item = ensp.item
                        INNER JOIN R ON ensp.rowpointer = R.rowpointer
                                        AND ensp.item = R.item;        
        END;

Open in new window

lcohan

How do you know it actually does not fire?
You could put a statement in there to raiseerror and see if that happens instead of the cursor code.
Also I would definitely NOT recommend using explicit transactions in SQL and in particular in triggers which are hidden SQL code.
Use BEGIN/TRY/CATCH instead if you really need to do that.

http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/
http://www.mssqltips.com/sqlservertip/1027/sql-server-2005-try-and-catch-exception-handling/

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

steven

ASKER

I am not sure why the trigger, I like the way you have it, and we created a Table in the db and tried to insert into it, a simple insert 'One record'. That table has nothing in it .

steven

ASKER

After SET NOCOUNT ON; we place insert into testable(item) and a simple select 'test1' . We figured that the table should at least have one value in it, but no luck nothing inserted. We also had the insert after the IF update(count_qty), hoping that it would insert, still with no luck...

Scott Pletcher

The trigger only specifies "AFTER UPDATE".

If you want it to run after INSERTs too, you'd have to code it "AFTER INSERT, UPDATE" and change the logic to match that (since a deleted row will not be available for an INSERT).

steven

ASKER

I will try that thank you

steven

ASKER

Thanks so much this worked!