Need Assistance 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

General SQL Server Forums

New to SQL Server Programming

Need Assistance with Trigger

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2010-03-09 : 09:00:56

I am attempting to use the trigger. Here is what I would like to happen. There is a column in the table PROGMODE1, the column name is MULTI. When an INSERT or UPDATE occurs to this table I would like to update the data in the column MULTI. Here is what I have so far but it seems when I try to run it it ends up going in to an infinate loop.

CREATE TRIGGER form_counts 
   ON  progmode1 
   AFTER UPDATE,DELETE
AS raiserror ('Notify Systems Admin of change',16,10)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
UPDATE    dbo.progmode1 set multi =
                          (SELECT     numberup
                            FROM          DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS
                            WHERE      cast(DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS.jobtask AS bigint) = dbo.progmode1.assigned_job)
WHERE     online = 0

    -- Insert statements for trigger here

END

The bold area above I had added and had remarked out all the rest to see if that would fix the infinate loop issue but it does not. So it has to be something in the way I am creating or calling the trigger. So could you please look at it and let me know where I went wrong. Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-09 : 09:12:20

[code]
UPDATE p
SET multi = d.numberup
FROM dbo.progmode1 p
INNER JOIN inserted i
ON p.<primary key of progmode1> = i.<primary key of progmode1>
INNER JOIN DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS d
ON p.assigned_job = cast(DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS.jobtask AS bigint)
WHERE p.online = 0
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2010-03-09 : 09:54:17

Ok I am not sure what this means but it looks very similar to the query that I already have that works. What I am trying to do is get the tigger to work. Also I am not sure where this table inserted comes into play at. So could you please tell me first how this will fix my trigger and second where the table inserted come into play at. Thank you.

quote:

Originally posted by khtan


UPDATE p
SET    multi = d.numberup
FROM   dbo.progmode1 p
       INNER JOIN inserted i  
       ON    p.<primary key of progmode1> = i.<primary key of progmode1>
       INNER JOIN DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS d 
       ON    p.assigned_job = cast(DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS.jobtask AS bigint)
WHERE     p.online = 0

KH
[spoiler]Time is always against us[/spoiler]

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 10:03:29

quote:
Originally posted by mrtweaver

Ok I am not sure what this means but it looks very similar to the query that I already have that works. What I am trying to do is get the tigger to work. Also I am not sure where this table inserted comes into play at. So could you please tell me first how this will fix my trigger and second where the table inserted come into play at. Thank you.

quote:
Originally posted by khtan

UPDATE p
SET multi = d.numberup
FROM dbo.progmode1 p
INNER JOIN inserted i
ON p.<primary key of progmode1> = i.<primary key of progmode1>
INNER JOIN DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS d
ON p.assigned_job = cast(DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS.jobtask AS bigint)
WHERE p.online = 0

KH
[spoiler]Time is always against us[/spoiler]

Boss..could you please look for Basics of Trigger works..so that would make you understand very easily instead of if we write as few lines as of this..again you have doubts..it's better to self read about this..if you are stuck on that,,please come back here..post your doubts on after reading..please do that...

Thanks,
Haroon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:14:07

quote:
Originally posted by mrtweaver

Ok I am not sure what this means but it looks very similar to the query that I already have that works. What I am trying to do is get the tigger to work. Also I am not sure where this table inserted comes into play at. So could you please tell me first how this will fix my trigger and second where the table inserted come into play at. Thank you.

quote:
Originally posted by khtan

UPDATE p
SET multi = d.numberup
FROM dbo.progmode1 p
INNER JOIN inserted i
ON p.<primary key of progmode1> = i.<primary key of progmode1>
INNER JOIN DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS d
ON p.assigned_job = cast(DJT_MT_INFO_TO_PRODTRACK_ALL_JOBS.jobtask AS bigint)
WHERE p.online = 0

KH
[spoiler]Time is always against us[/spoiler]

First thing to do is to try the given update instead of what you've now

Re. Inserted its a temporary table which is used by trigger code . It contains the new/modified values in case of INSERT/UPDATE action and the structure of table will be same as structure of your table on which trigger is built. So in above case join with inserted table will ensure you take only the records that were affected by recent insert/update action

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources