Solved

Trigger is lock a table

Posted on 2015-01-20
8
174 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 100 total points
Comment Utility
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 200 total points
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:Igor Nesralla
Comment Utility
How to better pratice to implement those triggers ?! can you help me !?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 200 total points
Comment Utility
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 200 total points
Comment Utility
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
Comment Utility
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 69

Accepted Solution

by:
ScottPletcher earned 200 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Server 2008 Cluster Fail-over Errors 5 39
Sort by Month and Year - SQL 3 21
Log Backup 2 11
SQL Inner Join Vs SubQueries 9 23
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now