Trigger Problem

[jgoodman00]

I have the following Trigger:
ALTER Trigger ADOPT_INS_TRG1
ON dbo.ADOPT
For Insert

NOT FOR REPLICATION

As

INSERT INTO dbo.tblInspection (RefNo)
SELECT RefNo FROM inserted INNER JOIN tblHierarchy
ON inserted.HierarchyID = tblHierarchy.HierarchyID
WHERE dbo.tblHierarchy.CivilsAsset = 0


INSERT INTO dbo.tblRemarks (RefNo)
SELECT RefNo FROM inserted INNER JOIN tblHierarchy
ON inserted.HierarchyID = tblHierarchy.HierarchyID
WHERE dbo.tblHierarchy.CivilsAsset = 0

DECLARE @sql varchar(100)

SELECT @sql=
'Insert INTO dbo.tblAsset' + dbo.tblHierarchy.FormName + '(RefNoL) ' +
'Values(''' + inserted.RefNo + ''')'
FROM dbo.tblHierarchy JOIN inserted
ON dbo.tblHierarchy.HierarchyID = inserted.HierarchyID


EXEC(@SQL)

This works fine until a 'Civils Asset' is encountered, at which point the program returns the 'another user has modified record...' error.

If I swap the position of the two static insert statements, so that they execute after the dynamic statement, it allows the user to insert records where [CivilsAsset] = 1. However, if the user attempts to insert a record where [CivilsAsset] = 0 it errors, because it cannot make the dynamic insert until the change on the [ADOPT] table has been comitted (FK problem).

Any suggestions? James Goodman MCP

http://www.james-goodman.co.uk

 

[MikePP]

James

Interesting question, and I don't know the answer.

What I find most interesting is your execution of a string within a stored procedure (@sql).

Is this something you do regularly?

What problems do you get?

Yours very interested

Mike Mike Watson
Providing Systems and Software for you
- Software for Banking
- Systems for the Power Industry

 

[Inwoner]

The problem is with the EXEC statement.

Remember that a Stored Procedure always works within it's own transaction.

Also remember that an EXEC statement execute within a new context (kind of like a new transaction). Try greating a #Temp table, and then accessing that same table on the same connection through EXEC....you will see that the EXEC cannot access the #Temp table because it executes within a new context. I would also think that you cannot access your INSERTED table through EXEC

Now putting this information together, you will see that you are within a transaction. When the EXEC statement executes, it is like a new connection. Your tables are actually locked through the Stored Proc, and now the EXEC statement tries to change this data when it is locked.

 

[AWithers]

I think your the build of you @sql is wrong, the last two lines would not have been included, the statement below should be better

SELECT @sql=
'Insert INTO dbo.tblAsset' + dbo.tblHierarchy.FormName + '(RefNoL) ' +
'Values(''' + inserted.RefNo + ''')
FROM dbo.tblHierarchy JOIN inserted
ON dbo.tblHierarchy.HierarchyID = inserted.HierarchyID'

I always find it best to put in a print @sql statement whilst debugging to ensure that the SQL is as you need when you come to execute it of course you can also syntax check it in this way

Andy