[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Igor NesrallaAuthor 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 NesrallaAuthor 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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