Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update Trigger Sql

Posted on 2014-11-05
8
214 Views
Last Modified: 2014-11-06
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
0
Comment
Question by:spoye
8 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40424269
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

0
 
LVL 39

Expert Comment

by:lcohan
ID: 40424270
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/
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40424313
The trigger is firing, unless it's disabled.  But explicitly committing a transaction inside a trigger could cause all kinds of issues, since you're already in a transaction when the trigger starts, and SQL Server doesn't have true embedded transactions.

Cursors should be avoided if at all possible, especially inside of triggers.

Try this code instead:


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

   if update(count_qty)
   begin

        update ensp
             set cal_count = ins.count_qty * d.us_length / 12
             from inserted ins
               inner join deleted del on del.item = ins.item and del.rowpointer = ins.rowpointer
               inner join ens_phyinv ensp on ensp.rowpointer = ins.rowpointer and ensp.item = ins.item
               inner join ens_dimmain d on d.item = ins.item
             where
               --verify that *this* row's count_qty changed before processing it
               isnull(ins.count_qty,-555) <> isnull(del.count_qty,-555) and
               substring(ins.item,4,1) in ('1','3') and
               ins.count_qty <> 0 and
               d.us_length <> 0

   end --if
 GO --end of trigger code
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:spoye
ID: 40424459
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 .
0
 

Author Comment

by:spoye
ID: 40424968
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...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40425031
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).
0
 

Author Comment

by:spoye
ID: 40426068
I will try that thank you
0
 

Author Closing Comment

by:spoye
ID: 40426869
Thanks so much this worked!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question