SQL Trigger Question - 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

SQL Trigger Question

Author

Topic

madd0g17
Starting Member

8 Posts

Posted - 2010-05-03 : 10:19:27

So I have a SQL table named dbo.workorder that has 2 important columns we need to check. If the "supervisor" is null, then we need to look at the location (in this specific case, the first 3 letters of the location) and assign the supervisor accordingly. However, if both the supervisor AND location are null, we need to set the supervisor to a special person who will review the WO and take the necessary action.

Here's what I have so far:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.assignsupervisor
   ON  dbo.workorder
   AFTER INSERT
   
AS
   
BEGIN
	SET NOCOUNT ON;
    
	IF EXISTS ( SELECT * FROM dbo.workorder WHERE supervisor IS NULL AND location IS NULL )
		BEGIN			
			UPDATE dbo.workorder SET supervisor='JOHNDOE'
		END
	IF EXISTS ( SELECT * FROM dbo.workorder WHERE supervisor IS NULL AND ( location like 'ABC%' OR location like 'CDE%' ) )
		BEGIN
			UPDATE dbo.workorder SET supervisor='JANEDOE'
		END
			
END
GO

Does this code look correct? And how well will it handle multiple rows being inserted? I am brand new to triggers so any and all help is greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:22:26

1.you dont have BEFORE. It should be INSTEAD OF.
2.Also you're hardcoding the values which is not generic.
3.The update has to be done on your actual table and not on the temporary table inserted.

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

madd0g17
Starting Member

8 Posts

Posted - 2010-05-03 : 10:39:21

Thanks for the prompt reply! I have updated my main post to reflect my updated code. Unfortunately we have to hard code the values since the people who are supervisors change from time to time and we dont have a nice pretty front-end that allows us to update a table with who is supervising what.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:43:16

Nope you need to use join with inserted table else you'll end up updating all records in table always

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

madd0g17
Starting Member

8 Posts

Posted - 2010-05-03 : 10:44:34

Could you provide me with an example perhaps? I'm not sure how I would use a join in this instance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:48:49

[code]SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.assignsupervisor
ON dbo.workorder
AFTER INSERT

AS

BEGIN
SET NOCOUNT ON;

UPDATE wo
SET wo.supervisor='JOHNDOE'
FROM dbo.workorder wo
INNER JOIN INSERTED i
ON i.PK = wo.PK
WHERE i.supervisor IS NULL
AND i.location IS NULL

UPDATE wo
SET wo.supervisor='JANEDOE'
FROM dbo.workorder wo
INNER JOIN INSERTED i
ON i.PK = wo.PK
WHERE i.supervisor IS NULL
AND ( i.location like 'ABC%' OR i.location like 'CDE%' )

END
GO
[/code]

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

madd0g17
Starting Member

8 Posts

Posted - 2010-05-03 : 10:57:40

I'm getting an error about i.PK and wo.PK not being valid column names. If I'm understanding correctly, I could join it on any column that would be the same for both the inserted and real tables (in this case, probably the Work Order # or something like that)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:59:47

Pk means your primary key column. replace it with your actual primary key

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

madd0g17
Starting Member

8 Posts

Posted - 2010-05-03 : 11:07:57

Ah OK! I had seen it in other examples but wasn't sure! Thanks! It's working beautifully right now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 11:08:51

cool...you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources