December 28, 2011 at 5:49 am
Hi
I'm having a problem in SQL SERVER 2008. Hope u guys can help.
1 - A Net application starts a transaction.
2 - Call a SP inside the transaction
3 - The SP inserts lines in 2 different tables (Tb1 and Tb2).
4 - Tb2 has a 2 triggers (insert update and delete).
5 - Trigger calls a view and updates the line (inserted by SP) in Tb1. The function uses Tb1 and Tb2 inserted lines Data.
In my programming enviroment it works.
As soon as a put it in the client machine everything stops (trigger not fired and blocks tables)
Any ideas or best options?
Answering some douts.
-No, i can't do the trigger as part of the SP. Other SPs insert in Tb2 and don't insert in Tb1, also i have to allow delete and update (by any means including editing table direcly) of Tb2 lines.
The trigger on Tb2 should allow me not to worry about data integrity between Tb1 And Tb2.
-The Trigger
CREATE TRIGGER tr_Relav ON RELav AFTER INSERT,UPDATE AS
DECLARE @sql nVARCHAR(max)
SET @sql=''
SELECT @sql=@sql+'Update REL Set REL.PendL = (Select Qtd From PendentesRE_Net Where NumDoc =' + Convert(varchar,NumDoc) + ' And NumLinha=' + Convert(varchar,NumLinha) + '); ' FROM Inserted
EXECUTE sp_executesql @sql
REL is Tb1
ReLav is Tb2
December 28, 2011 at 6:19 am
I would incorporate the trigger code in the procedures if possible.
When you end up with this kind of trigger it's a symptom of a bad relational design. Is refactoring your database model an option?
That said, the trigger could be rewritten like this, to avoid dynamic sql and transactions issues:
CREATE TRIGGER tr_Relav ON RELav
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE REL
SET REL.PendL = (
SELECT Qtd
FROM PendentesRE_Net
WHERE NumDoc = Inserted.NumDoc
AND NumLinha = Inserted.NumLinha
FROM INSERTED
END
-- Gianluca Sartori
December 28, 2011 at 7:30 am
Hi GianLuca,
Tkx for your reply.
As i said in the post i can't put the code in the SP (Tb2 is changed in varies situations).
DataBase is old (came from before Access 95) and has Gbs of Data. Yes, i would like to substantialy change it, but, no time.
I tried your TRIGGER,
ALTER TRIGGER [dbo].[tr_Relav] ON [dbo].[RELaV] AFTER INSERT,UPDATE AS
Begin
SET NOCOUNT ON;
UPDATE REL
SET REL.PendL =(Select (
SELECT Qtd
FROM PendentesRE_Net
WHERE NumDoc = a.NumDoc
AND NumLinha = a.NumLinha) From Inserted a)
End
and seems to be working (just have to confirm performance)
Tkx again,
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply