Problem with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with trigger

Author

Topic

Azazel27
Starting Member

3 Posts

Posted - 2006-10-09 : 10:06:35

Hello,

I've made a trigger that listen to a table and waits for 12 sequencial inserts. ( The code is as follows )

CREATE TRIGGER trgInsertNewWorkflow_XRX

ON dbo.Some_Table

after INSERT

AS

Declare @ID_WF0 varchar(50)

set @ID_WF0 = (Select MAX(ID) from dbo.DWE_Workflows WITH(NOLOCK))

IF (Select count(*) from dbo.DWE_WF_Attributes where Workflow_ID = @ID_WF0)=12
Exec XRX_InsertRoutingHist

The thing is that in a certain database, SQP 2000 w\ SP4, when I put 12 the program only inserts 11. If I put 11, the program only inserts 10, and so on.
I don't know why this is happenning.
The query that is done with the if, inserts values in another table.

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-09 : 10:56:56

TRIGGERS don't listen, they fire based on an event

Could you elaborate on what you are trying to do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Azazel27
Starting Member

3 Posts

Posted - 2006-10-09 : 11:14:53

Yes,

Basically I have an application that when an action is made, writes in this table 12 rows that indicate the attributes of a certain action in the application. I have to wait until the 12 rows are written in order to start the other SP.
This SP makes some selects from this table in order to put them in another structure of tables in the same Database.

I cannot isolate the action in the application, this is why I'm using a trigger in the database.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-09 : 12:17:37

This is a really bad thing to try to do with a trigger, since you are attempting to span transactions. It is just not what a trigger is for.

CODO ERGO SUM

Azazel27
Starting Member

3 Posts

Posted - 2006-10-09 : 12:24:20

I really don't like the solution, but this is the best and only way to do it.
I tried to do thru the application, it would be the best solution but this was just impossible. So this works in my SQL Server, but in the customers Database just won't work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources