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

Transact-SQL (2000)

Trigger

Author

Topic

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-01 : 11:37:55

Can anyone give me an example how I can call a stored procedure from a trigger and pass two parameters. Please help. Here is the scenario am trying to do:

execute a stored procedure pass two values and fire a trigger (on Table1)

Once trigger done will pass two parameters to second stored procedure and execute it

second procedure will fire trigger on Table2 and trigger on table 2 will pass two parameters to third stored procedure

third stored procedure will fire trigger on Table3

All is UPDATE

Thanks in advance

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 12:28:32

A trigger is called with a "set" of records (in a pseudo table called "insert").

So you would have to either loop round, or Cursor, round the "insert" table and call the SProc within the loop in order to process all the records.

Kristen

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-01 : 12:40:25

Kristen,

As you may understand from my login name am a newbie. I need an example on like Table1, Table2, Table3

StoredProcedure1 --> Table1 (Trigger1) --> StoredProcedure2 --> Table2 (Trigger2) --> StoredProcedure3 --> Table3

All Updates

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-01 : 13:25:32

you need to explain it a little better what you're trying to do.

Trying to head down one direction as a solution without us know what you're trying to accomplish could be a big waste of time.

Mostly because what you're proposing is a very bad idea.

Follow my hint below.

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

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-08-01 : 14:25:57

Instead of nesting triggers perhaps you can put all of your update logic inside of a single stored procedure. It will probably be easier to maintain the long run.

Dustin Michaels

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-01 : 16:46:20

OK,

I make it much simple. Here is trigger on table1 (Job)

CREATE TRIGGER [JobUpdate] ON [dbo].[Job]
FOR UPDATE
AS

IF UPDATE(ItemStateID)
BEGIN
UPDATE Job
SET ItemStateID = inserted.ItemStateID
FROM Job, inserted
WHERE inserted.JobID = Job.JobID
UPDATE JobTask
SET ItemStateID = inserted.ItemStateID
FROM JobTask, inserted
WHERE inserted.JobID = JobTask.JobID
UPDATE JobTaskTaskProperty
SET ItemStateID = inserted.ItemStateID
FROM JobTaskTaskProperty, JobTask, inserted
WHERE JobTaskTaskProperty.JobTaskID = JobTask.JobTaskID
END

Here is the problem first two works properly but third one not.

Third table (JobTaskTaskProperty)

3 JobTaskTaskPropertyID int 4 0
0 JobTaskID int 4 0
0 TaskPropertyID int 4 0
0 TaskPropertyValueID int 4 1
0 IsConfigurable bit 1 0
0 InsertedDateTime datetime 8 0
0 UpdatedDateTime datetime 8 0
0 ItemStateID int 4 0

This table also have it's own trigger:

CREATE TRIGGER [JobTaskTaskProperty_SetUpdatedDateTime] ON [dbo].[JobTaskTaskProperty]
FOR UPDATE
AS
UPDATE
JobTaskTaskProperty
SET
UpdatedDateTime = GETDATE()
WHERE
JobTaskTaskPropertyID IN (SELECT JobTaskTaskPropertyID FROM Inserted)

COMMIT TRANSACTION

HELP!

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-01 : 16:47:42

FYI

First 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

Second 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
0 ItemStateID int 4 0

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 00:41:42

[code]
UPDATE Job
SET ItemStateID = inserted.ItemStateID
FROM Job, inserted
WHERE inserted.JobID = Job.JobID
[/code]
This is superfluous isn't it? The UPDATE will already have happended on teh udnerlying table
By the by, you probably should change to using JOIN syntax
[code]
UPDATE Job
SET ItemStateID = inserted.ItemStateID
FROM Job
JOIN inserted
ON inserted.JobID = Job.JobID
[/code]
on the third one
[code]
UPDATE JobTaskTaskProperty
SET ItemStateID = inserted.ItemStateID
FROM JobTaskTaskProperty, JobTask, inserted
WHERE JobTaskTaskProperty.JobTaskID = JobTask.JobTaskID
[/code]
you have only specified a relationship between JobTaskTaskProperty and JobTask, and NOT with "inserted" - so you will get a cartesian JOIN
[code]
UPDATE JobTaskTaskProperty
SET ItemStateID = inserted.ItemStateID
FROM JobTaskTaskProperty
JOIN JobTask
ON JobTask.JobTaskID = JobTaskTaskProperty.JobTaskID
JOIN inserted
ON inserted.JobID = JobTask.JobID
[/code]
putting the table owner in (blue bits) helps the efficiency in SQL Server, and is "good practice"

Note that this trigger will NOT be restricted to rows where ItemStateID has changed. It WILL only make changes if at least one row in the update-set has a change in that column, but if other rows in the update have NOT changed that column your UPDATE will still be executed.

From an efficiency point of view your code is fine, however if it is important that the UPDATEs are only executed for rows where the value of ItemStateID has changed you will also ned to JOIN the "deleted" pseudo-table and compare the value of deleted.ItemStateID with inserted.ItemStateID (watching out for NULL rows in deleted table for newly inserted rows)

Kristen

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-02 : 11:09:46

Thank you so much Kristen,

I try to add deleted but could not success. Can you help?

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-02 : 11:15:15

Kristen
PS: Third table still not updated by trigger while 1.st and 2.nd updated succesfully

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-02 : 11:17:03

current version of trigger on Job Table:

CREATE TRIGGER [JobUpdate] ON [dbo].[Job]
FOR UPDATE
AS

IF UPDATE(ItemStateID)
BEGIN
UPDATE Job
SET ItemStateID = inserted.ItemStateID
FROM Job
JOIN inserted
ON inserted.JobID = Job.JobID

UPDATE JobTask
SET ItemStateID = inserted.ItemStateID
FROM JobTask, inserted
WHERE inserted.JobID = JobTask.JobID

UPDATE JobTaskTaskProperty
SET ItemStateID = inserted.ItemStateID
FROM JobTaskTaskProperty
JOIN JobTask
ON JobTask.JobTaskID = JobTaskTaskProperty.JobTaskID
JOIN inserted
ON inserted.JobID = JobTask.JobID

END

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-02 : 11:26:44

UPDATE
It works, problem was about a "COMMIT TRANSACTION" at the end of first trigger on Table2.

Since it is important that the UPDATEs are only executed for rows where the value of ItemStateID has changed am trying to add deleted.ItemState issue. Help needed.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 11:28:26

Add to the FROM bit:

LEFT OUTER JOIN deleted D
ON D.JobID = JobTask.JobID

and to the WHERE bit:

WHERE (D.JobID IS NULL OR (D.ItemStateID IS NOT NULL AND I.ItemStateID IS NULL) OR (D.ItemStateID IS NULL AND I.ItemStateID IS NOT NULL) OR D.ItemStateID <> I.ItemStateID)

and that will include records which are a) new or b) the ItemStateID column has changed

Kristen

Newbie2005
Starting Member

29 Posts

Posted - 2005-08-02 : 14:13:13

Kristen,
appologize but which part of trigger? I mean which UPDATE section 1, 2, or 3?

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 15:08:02

Which ever ones you need to only ake an action when the column changes. (I expect that in practice that is all of them, but your call!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources