Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Trigger is lock a table

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
Igor Nesralla
Asked:
Igor Nesralla
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
Daniel WilsonCommented:
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
 
LowfatspreadCommented:
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
 
Scott PletcherSenior DBACommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Igor NesrallaProprietárioAuthor Commented:
How to better pratice to implement those triggers ?! can you help me !?
0
 
Scott PletcherSenior DBACommented:
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
 
LowfatspreadCommented:
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
 
Igor NesrallaProprietárioAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now