Triggers and Stored Procedure - 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

Transact-SQL (2000)

Triggers and Stored Procedure

Author

Topic

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-19 : 12:35:29

Here is my problem:

I have a stored procedure like this:

CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int, @stateParameter int) AS
UPDATE Job
SET ItemStateID = @stateParameter
WHERE JobID = @jobID

GO

and this stored procedure runs against this table:

3 JobID int 4 0
0 JobName varchar 64 0
0 JobDescription varchar 256 1
0 InsertedDateTime datetime 8 0
0 UpdatedDateTime datetime 8 0
0 ItemStateID int 4 0

This table has a trigger like this:

CREATE TRIGGER [jobTrigger] ON [dbo].[Job]
FOR UPDATE
AS
declare @jobID int
declare @stateParameter int

SELECT @stateParameter = 2
SELECT @jobID = (SELECT jobID FROM Job WHERE jobID = Job.jobID);

EXEC dbo.usp_jobTaskDelete @jobID, @stateParameter
RETURN

Here is the usp_TaskDelete:

CREATE PROCEDURE [dbo].[usp_jobTaskDelete]
(@jobID int, @stateParameter int) AS

UPDATE JobTask
SET ItemStateID = @stateParameter
WHERE JobID = @jobID
GO

And then JobTask table has a trigger to fire another stored procedure:

CREATE TRIGGER [jobTaskTaskPropertyDelete] ON [dbo].[JobTask]
FOR UPDATE
AS
declare @jobID int
declare @jobTaskID int
declare @stateParameter int

SELECT @stateParameter = 2
SELECT @jobTaskID = (SELECT jobTaskID FROM JobTask WHERE jobID = JobTask.jobID);

EXEC dbo.jobTaskTaskPropertyDelete @jobTaskID, @stateParameter

Here is the JobTask table:
3 JobTaskID int 4 0
0 JobID int 4 0
0 TaskID int 4 0
0 JobTaskName varchar 64 0
0 JobTaskDescription varchar 256 1
0 IsConfigurable bit 1 0
0 Ordinal int 4 0
0 InsertedDateTime datetime 8 0
0 UpdatedDateTime datetime 8 0

And finally here is the jobTaskTaskPropertyDelete stored procedure:

CREATE PROCEDURE [dbo].[usp_jobTaskTaskPropertyDelete]
(@jobTaskID int, @stateParameter int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = @stateParameter

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE jobTaskID = JT.JobTaskID
AND JT.JobID = JobID)

GO

At the end I'm receiving an sql exception like: sub query returns more than 1 value.....

HELP!!!

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-19 : 13:04:36

Basically,

I want to have first stored procedure and once this stored procedure update a record trigger on job table must be fired and call second procedure using two parameters and then once second procedure run trigger on JobTask table must fired and call third stored procedure.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-19 : 13:17:50

without going into the design of your tables and business rules...the error is caused by these lines in your triggers. Because they will return every row in their tables:
>>SELECT @jobID = (SELECT jobID FROM Job WHERE jobID = Job.jobID)
>>SELECT @jobTaskID = (SELECT jobTaskID FROM JobTask WHERE jobID = JobTask.jobID)
and you are trying to assign the result to a single variable. That's the "subquery returns more than 1 value" issue.

Its good practice to design your triggers knowing that it's possible that more than one record could be inserted/updated at a time (yes, even though your SPs only act on one record at a time)

take a look at programming triggers in BOL, you should be making use of the inserted and deleted tables.

Be One with the Optimizer
TG

giovi2002
Starting Member

46 Posts

Posted - 2005-07-20 : 13:13:23

quote:
Originally posted by TG

take a look at programming triggers in BOL, you should be making use of the inserted and deleted tables.

Be One with the Optimizer
TG

But the prob is the inserted and deleted tables can also contain recordsets, so probably you will need a cursor to run through each record .

I never understood the design of sql server on recordsets, physically every record gets inserted individually because constraints will be checked per record. After this per record check the whole set will be put in the inserted table which is quite a stupid thing. Why not putting them into the inserted table one by one?
Or two objects you can refer to, like 'inserted for each' or 'inserted recordset', it would be a nice enhancement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:16:53

Here's a blog entry that I wrote which discusses the problem mentioned:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Note the use of a JOIN instead of a cursor.

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-20 : 15:27:45

>>Why not putting them into the inserted table one by one?

Graz, if the next suggestion is to be able to put triggers on the inserted and deleted tables, please either lock this topic or shoot me.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:29:56

quote:

Why not putting them into the inserted table one by one?

Because of performance! Doing things row by row makes SQL Server very, very slow. Avoid making unnecessary repeated calls to SQL Server when it can be done in one query and also avoid cursors and WHILE loops if there is a set-based solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources