1

I have a trigger for executing two procedures. 

ALTER TRIGGER [dbo].[TRG_SP_SYNCH_CAB]
   ON [VTBO_INTERFACE].[dbo].[T_TRIGGER_TABLE_FOR_SYNCH]
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO T_TRIGGER_TABLE_FOR_SYNCH (DT) 
    VALUES (GETDATE());

    exec PUMPOMAT_HO.DBO.SP_CM_TransferCAB
    exec PUMPOMAT_HO.DBO.SP_CM_UpdateCAB

END

Execution time for two procedures is 5 mins. When I try to insert a value to T_TRIGGER_TABLE_FOR_SYNCH table, my other tables which are used in stored procedures are locked along 5 mins. But when I try to execute two procedures directly like 

exec SP_CM_TransferCAB
exec SP_CM_UpdateCAB

No lock happens. What should I write in trigger to avoid of table locks.

Thanks.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Hakan Kara
  • 433
  • 1
  • 6
  • 16
  • 2
    *"Execution time for two procedures is 5 mins"* - Then these two procedures do not belong in a trigger!! I would start by addressing why you need to execute 5 minute long procedures for every insert before you address the issue of locking. – GarethD Oct 15 '14 at 08:58
  • I need to execute the sql server stored procedure inside an oracle procedure. There is a dblink between two servers. I am not able to execute sp directly, but I am able to insert a record. That is an alternative way for me. – Hakan Kara Oct 15 '14 at 09:03

1 Answers1

0

Try by calling the second procedure inside(end of) the first procedure since I see no parameters are given.

Is this table [VTBO_INTERFACE].[dbo].[T_TRIGGER_TABLE_FOR_SYNCH] used in any of the procedure?

You should try to change the design/data flow to mimic this procedure call.

Recursive
  • 954
  • 7
  • 12
  • Even if I use only one procedure in trigger, the lock still happens. For example the lock takes 3 mins if I use only the first procedure. – Hakan Kara Oct 15 '14 at 09:51