Trouble by using a 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)

Trouble by using a trigger

Author

Topic

Jaap
Starting Member

19 Posts

Posted - 2004-10-05 : 08:29:56

Hello there,

I have made an procedure named prc_insActionCustumer @p1
When I run this procedure by Debuging there is no problemm. The procedure runs to the end (last action did complete well)
Total actions are
1 INSERT record at table1
2 INSERT record at table2
3 INSERT record at table3
4 INSERT record at table2
5 INSERT record at table3
6 INSERT record at table4
7 -12 INSERT 5 records at table5
13-17 INSERT 4 records at table6
18 UPDATE recort at table4 (inserted at step 6)

Next step is to add thes procedure in een After Insert trigger.

I mad this by openening a cursor on the table Inserterd and looping WHILE @@FETCH_STATUS = 0

In the loop I EXECUTE the procedure prc_insActionCustumer
The Same steps should be executed

1 INSERT record at table1
2 INSERT record at table2
3 INSERT record at table3
4 INSERT record at table2
5 INSERT record at table3
6 INSERT record at table4
7 -12 INSERT 5 records at table5**
** After the 3th insert the procedure ends without any error message.
Next steps do not be executed.

The inserts are INSERT INTO () VALUES() statments
The update is een UPDATE tbl.. SET ... WHERE Key = @Key

What can be the problem. Or do you know how I can findout what is the error et this point.

NOTE: Both tests are be done with exectly the same data.

I hope somebody can help

Jaap

ADD: When using other data that contains shorter loops -> less records to insert the procedure does stop later in the process. (by the UPDATE. The update is not executed. The line before does. (I can see this in the Current SQL Server Log. Before the UPDATE statement I write a line to the log by a Master.dbo.xp_logevent statement.)

Is ther maybe a memory problem or is there a memory limmit for trigger events?

Jaap

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-05 : 09:01:52

could it be a recursion problem ?
if you have put the prc_insActionCustumer in an after insert trigger (for example for table2) and prc_insActionCustumer makes an insertion in table2 then that should be an infinite loop ,right?

Jaap
Starting Member

19 Posts

Posted - 2004-10-05 : 09:11:08

No this is not the problem The trigger and the INSERT actions are not on the same Table. All tables are created in the same filegroup. May be I recived the maximum of locks in a file group?

quote:
Originally posted by dev45

could it be a recursion problem ?
if you have put the prc_insActionCustumer in an after insert trigger (for example for table2) and prc_insActionCustumer makes an insertion in table2 then that should be an infinite loop ,right?

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-05 : 09:16:49

can you post the procedure ?

Jaap
Starting Member

19 Posts

Posted - 2004-10-05 : 09:50:55

I can, but it the procedure executes other procedures (total there are 10 procedures and some functions.)

Still the problemm is that the procedure works in debug mode or when I run it from Query Analyser 'exec prc_insActieRelatie 12345'
dbo.tblActieRelatie contains a autonummer and a quantity and some other ForeignKeys
How bigger the quantity how more inserts are needed.
The procedure creates the records for the count of boxes needed for transport. 1 box contains max 1500 pieces so 6000 needs 4 boxes. By using a quantity (up to 4500) of 3 boxes the triggers works.
By using the Query analyser a quantity of 1000000 (690 boxes) is no problemm.

But here are the trigger and the main procedure

TRIGGER trg_insActieRelatie
ON dbo.tblActieRelatie
AFTER INSERT
AS

BEGIN
DECLARE @IDActieRelatie int
DECLARE crs_Inserted CURSOR
LOCAL
FAST_FORWARD
READ_ONLY
FOR SELECT IDActieRelatie
FROM Inserted

DECLARE @crs_Inserted CURSOR
SET @crs_Inserted = crs_Inserted
OPEN @crs_Inserted
FETCH FROM @crs_Inserted
INTO @IDActieRelatie

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC prc_insActieRelatie @IDActieRelatie

FETCH NEXT FROM @crs_Inserted
INTO @IDActieRelatie
END
CLOSE @crs_Inserted
DEALLOCATE @crs_Inserted
DEALLOCATE crs_Inserted
END

Procedure prc_insActieRelatie @IDActieRelatie int
AS

BEGIN
DECLARE @outAantalGratis real
DECLARE @outAantalBetaald real
DECLARE @VerzendingVolgnummer int
DECLARE @IDOrder int

SET @outAantalGratis = 0
SET @outAantalBetaald = 0
EXEC dbo.prc_ActieRelatieAantal @IDActieRelatie, @outAantalGratis OUTPUT, @outAantalBetaald OUTPUT

IF @outAantalGratis <> 0
EXEC dbo.prc_addActieOrderRegel @IDActieRelatie, @outAantalGratis , 1
IF @outAantalBetaald <> 0
EXEC dbo.prc_addActieOrderRegel @IDActieRelatie, @outAantalBetaald , 0
SELECT @IDOrder = IDOrder
FROM dbo.tblActieOrderRegel
WHERE @IDActieRelatie = @IDActieRelatie
SET @VerzendingVolgnummer = dbo.fun_intVerzendingVolgnummer(@IDOrder, 1)

EXEC prc_AddOrderVerzending @IDOrder, 1, @VerzendingVolgnummer, 0, 0
END

quote:
Originally posted by dev45

can you post the procedure ?

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-06 : 01:31:03

if it runs when you use query analyzer then there should be no problem with the procedure and the way it is functioning. Maybe there is a problem with the front end application that uses it. (eg : command timeout not enough ?)

Jaap
Starting Member

19 Posts

Posted - 2004-10-06 : 04:01:37

Nice option but not the right one.

Time from start to cancel is not every time the same.
It variates between in 1 second to 2.sec 40.milsec
I write before and after every Write action(Insert or Update) a record to SQL Server logfile so I can see it whats happening.
So it is not a question of time. What to me have to get the attention is that the cancel is (nearly) on the same place. After insert of the 2th or 3th box record.

If your suggestion should be right then should a record inserted in the table by SQL Server Enterprice manager goes well? It is not. Cancels on the same position. (And not commiting the record. The identity is used but the record is deleted. This happens also sometimes in my MS-Access application but not every time)

An other test is inserting the same record by an INSERT INTO in Query Analyser.
INSERT INTO [dbBibs].[dbo].[tblActieRelatie](
[IDRelatie],
[IDActie],
[IDAssSamenstelling],
[DatumInvoer],
[ActieAantal])
VALUES(126127, 7, 930, getdate(), 10000)
go

Dont ask me why but this action completed succesful without canceling.

I also tested it whit a quantity of 1000000 pieces (690 boxes) and there is no problemm.

So the situation is now:
It works by
- An insert by Query Analyser
- An insert by MS-ACCESS Quantity < 4500 pieces
- An insert by SQL Server Enterprice manager (2000 pieces -> 2 boxes)

It cancels by
- An insert by MS-ACCESS Quantity > 4500 pieces -> more than tree records in the table contianing the boxes
- An Insert by SQL Server Enterprice Manager (3000 pieces - 2 boxes)

Jaap

quote:
Originally posted by dev45

if it runs when you use query analyzer then there should be no problem with the procedure and the way it is functioning. Maybe there is a problem with the front end application that uses it. (eg : command timeout not enough ?)

Jaap
Starting Member

19 Posts

Posted - 2004-10-06 : 05:07:06

Hello dev45

The problem is not solved as the way I do like but with your help I have found an other way to fix it.

Now I execute the procedure not by an trigger on the table but by the AFTER INSERT event in MS Access. That works.

I still hope anyone can tell me whats my problem in future but for this moment I stop resarch on this.

Thanks

Jaap

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources