Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trigger is lock a table

Posted on 2015-01-20
8
Medium Priority
?
202 Views
Last Modified: 2015-01-22
I had a trigger that fires after inserted(when the ERP software creates a "finance record", but when i`ve update this record on ERP software throws a error : "user's locks table "

Why this happend ?
T-MOVIMENTACAO-NUCLEUS-INSERT-3.sql
T-MOVIMENTACAO-NUCLEUS-UPDATE.sql
0
Comment
Question by:Igor Nesralla
[X]
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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 300 total points
ID: 40561221
OK, please help me understand.  The insert trigger does a bunch of calculations and conditionally inserts into another table.  Right?  And causes no problems on its own, right?

The update trigger also does a bunch of calculations and updates the other table.  Right?  But causes a "lock up" condition?

Are there any triggers on t_Nucleus_Movimento ?

I'm concerned about recursive triggers causing the problem you describe.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 600 total points
ID: 40561320
which erp suite is this?

In general it is not a good idea to add triggers to bought in software packages, as you can never be sure of the effects on the internal processing , and are always at risk from the latest patches and upgrades.

 
i'm having trouble accessing the second trigger...

the first however
 makes the cardinal mistake of not catering for multiple row conditions

 and appears to have far more sql statements than absolutely necessary....

my  gut feel is that it should reduce to just an

Insert into .....
   select i.  ,.......
               case when ......... then   ........
                   when ..... then .....
                   ....
               end
   from inserted as i
  inner join tmov as o
    on i.idmov=o.idmov
  and i.....
  inner join .....

    etc

when writing triggers it is vital to be as efficient as possible, especially with 3rd party software over which you have no control /understanding of the update strategies in place.

i've just managed to view the update triggers and see its basically a copy of the insert...

with an update trigger its usually vital to actually confirm that a data change has physically taken place (since the trigger fires for each row replaced ... even if the data hasn't been modified)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40562450
Those triggers are bad/wrong!  You should NEVER use BEGIN TRAN / COMMIT TRAN in a SQL Server trigger.  SQL does not support true embedded transactions.  Since there is always a transaction active when a trigger is called, when you "COMMIT TRAN" you are committing the original (outer) transaction, NOT the one in your trigger.  This can cause serious issues and task failures.

Also, as noted above, the triggers need rewritten to use the inserted and deleted tables instead.

In short, that trigger code is truly awful and needs completely rewritten.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Igor Nesralla
ID: 40562922
How to better pratice to implement those triggers ?! can you help me !?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 600 total points
ID: 40563090
Here's the first trigger rewritten.  I strongly suspect it will need some tweaking, but it should get you close enough to complete it.


CREATE TRIGGER T_MOVIMENTACAO_NUCLEUS_INSERT  
 ON [dbo].TMOV

AFTER INSERT  

AS
SET NOCOUNT ON;

INSERT INTO [ACPOnline_nucleos_teste].[dbo].[t_Nucleus_Movimento]
    (dColigada, IdFilial, CodigoTipoMovimento, IdMov, NumeroMovimento, DataEmissao, ValorBruto,
    ValorLiquido, Fornecedor, Observacoes, CR, Requisitante, StatusRM, CompraEmergencial,
      StatusACP,EtapaAprovacao)

SELECT
    i.CODCOLIGADA, i.CODFILIAL, i.CODTMV, i.IDMOV, i.NUMEROMOV, i.DATAEMISSAO, i.VALORBRUTO,
    i.VALORLIQUIDO, FCFO.NOME, oa1.OBSERVACOES, oa2.CR, i.USUARIOCRIACAO, i.[STATUS], oa3.COMPEMERG,
    ca1.StatusACP, ca1.EtapaAprovacao
   
FROM inserted i
CROSS APPLY (
    SELECT CASE
           WHEN i.CODTMV = '1.1.11' THEN 3
           WHEN i.CODTMV = '1.1.25' THEN 8
           WHEN i.CODTMV = '1.1.16' THEN 8
           WHEN i.CODTMV = '1.1.17' THEN 1
           WHEN i.CODTMV = '1.1.01' THEN 1
           WHEN i.CODTMV = '1.1.28' THEN 1
           WHEN i.CODTMV = '1.1.23' THEN 3
           WHEN i.CODTMV = '1.1.05' AND i.[STATUS] = 'O' THEN 5
           ELSE 3 END AS StatusACP,
           CASE
           WHEN i.CODTMV = '1.1.11' THEN 1
           WHEN i.CODTMV = '1.1.25' THEN 1
           WHEN i.CODTMV = '1.1.16' THEN 1
           WHEN i.CODTMV = '1.1.17' THEN 1
           WHEN i.CODTMV = '1.1.01' THEN 1
           WHEN i.CODTMV = '1.1.28' THEN 1
           WHEN i.CODTMV = '1.1.23' THEN 1
           WHEN i.CODTMV = '1.1.05' AND i.[STATUS] = 'O' THEN 1
           ELSE 0 END AS MovimentoValido,
           0 AS EtapaAprovacao
) AS ca1
LEFT OUTER JOIN FCFO ON
    FCFO.CODCFO = i.CODCFO
OUTER APPLY (
    SELECT (CASE WHEN HISTORICOCURTO IS NOT NULL      
                 THEN HISTORICOCURTO      
                 ELSE HISTORICOLONGO END) AS "OBSERVACOES"      
    FROM (
          SELECT TMOVHISTORICO.HISTORICOCURTO,
          CAST(TMOVHISTORICO.HISTORICOLONGO AS NVARCHAR(MAX)) as "HISTORICOLONGO"
          FROM TITMMOV, TMOVHISTORICO      
          WHERE TITMMOV.IDMOV = i.IDMOV
          AND TITMMOV.CODCOLIGADA = i.CODCOLIGADA    
          AND TITMMOV.IDMOV = TMOVHISTORICO.IDMOV        
          AND TITMMOV.CODCOLIGADA = TMOVHISTORICO.CODCOLIGADA      
          GROUP BY TMOVHISTORICO.HISTORICOCURTO,
          CAST(TMOVHISTORICO.HISTORICOLONGO AS NVARCHAR(MAX))
    ) as y    
) AS oa1
OUTER APPLY (
    SELECT GCCUSTO.CODCCUSTO + ' - ' + GCCUSTO.NOME AS [CR]
    FROM GCCUSTO, TMOVRATCCU      
    WHERE TMOVRATCCU.IDMOV = i.IDMOV
    AND TMOVRATCCU.CODCOLIGADA = i.CODCOLIGADA    
    AND TMOVRATCCU.CODCCUSTO = GCCUSTO.CODCCUSTO        
    AND TMOVRATCCU.CODCOLIGADA = GCCUSTO.CODCOLIGADA    
) AS oa2
OUTER APPLY (
    SELECT COMPEMERG
    FROM TMOV, TMOVCOMPL
    WHERE TMOV.IDMOV = i.IDMOV
      AND TMOV.CODCOLIGADA = i.CODCOLIGADA
      AND TMOVCOMPL.IDMOV = TMOV.IDMOV
      AND TMOVCOMPL.CODCOLIGADA = TMOV.CODCOLIGADA
) AS oa3
WHERE
    ca1.MovimentoValido = 1

GO --end of trigger
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 600 total points
ID: 40563341
Scot's answer is what i envisioned , and i agree with his sentiments...

i was puzzled by
   SELECT (CASE WHEN HISTORICOCURTO IS NOT NULL       
                 THEN HISTORICOCURTO       
                 ELSE HISTORICOLONGO END) AS "OBSERVACOES"      
    FROM (
          SELECT TMOVHISTORICO.HISTORICOCURTO,
          CAST(TMOVHISTORICO.HISTORICOLONGO AS NVARCHAR(MAX)) as "HISTORICOLONGO"
          FROM TITMMOV, TMOVHISTORICO       
          WHERE TITMMOV.IDMOV = i.IDMOV
          AND TITMMOV.CODCOLIGADA = i.CODCOLIGADA    
          AND TITMMOV.IDMOV = TMOVHISTORICO.IDMOV         
          AND TITMMOV.CODCOLIGADA = TMOVHISTORICO.CODCOLIGADA       
          GROUP BY TMOVHISTORICO.HISTORICOCURTO,
          CAST(TMOVHISTORICO.HISTORICOLONGO AS NVARCHAR(MAX))

Open in new window

as i couldn't see why a group by clause would be needed.... especially as the original coding assumes a singleton result (its a a SET from a select...)  

i suspect that this is a prime candidate for improvement/simplification....

why do you need to group?

and isn't CASE WHEN HISTORICOCURTO IS NOT NULL      
                 THEN HISTORICOCURTO      
                 ELSE HISTORICOLONGO END

just coalesce(historicocurto,historicolongo)    ?
0
 

Author Comment

by:Igor Nesralla
ID: 40563505
Thank you so much everyone !!!

Can help me on a update trigger too !? Because i think there is a lock problem on a user change a record on ERP suite and update a table on my workflow aprove web app.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 600 total points
ID: 40564275
I can't right now, I don't have time.  I figured the example of the first one would be enough for you to tackle the second one.

@Lowfatspread: Good points.  But I wanted to provide the overall query structure, so I delayed any rewriting of subquery(ies) until after the main trigger code was rewritten and working.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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