Help with conditional SQL Trigger

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 2008 Forums

Transact-SQL (2008)

Help with conditional SQL Trigger

Author

Topic

mimuk
Starting Member

19 Posts

Posted - 2014-10-26 : 06:49:53

I have a Trigger which works as expected.

ALTER trigger [dbo].[trg_descupdate]
on [dbo].[NewProduct]
for insert
as
update NewProduct
set prod_c_description
= i.prod_c_family + ' ' + i.prod_name + ' ' + i.prod_c_type + ' H' + CAST(i.prod_c_height AS NVARCHAR) + ' P' +i.prod_c_potsize
from NewProduct l
Inner join inserted i
on i.prod_productid = l.prod_productid

however I would like to extend this with a condition
where prod_c_plantorpot = 'Plant' then the above fires

else

set prod_c_description
= i.prod_c_family + ' ' + i.prod_name + ' ' + i.prod_c_type + ' H' + CAST(i.prod_c_height AS NVARCHAR) + ' W' + CAST(i.prod_c_width AS NVARCHAR) + ' D' + CAST(i.prod_c_depth AS NVARCHAR)

I can't seem to get the case statement working correctly.

Many thanks in advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-26 : 07:11:31

[code]ALTER trigger [dbo].[trg_descupdate]
on [dbo].[NewProduct]
for insert
as

update NewProduct
set prod_c_description
= i.prod_c_family + ' ' + i.prod_name + ' ' + i.prod_c_type + ' H' + CAST(i.prod_c_height AS NVARCHAR) +
case when i.prod_c_plantorpot = 'Plant' then ' P' +i.prod_c_potsize else '' end
from NewProduct l
Inner join inserted i
on i.prod_productid = l.prod_productid[/code]

mimuk
Starting Member

19 Posts

Posted - 2014-10-26 : 07:23:21

Thank You James K
Perfect!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-26 : 10:47:44

Also make sure your NVARHAR type casting involves a data length. Don't rely on the default 30 length value.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-26 : 11:00:03

Here is the trigger code

ALTER TRIGGER	dbo.trg_DescUpdate
ON		dbo.NewProduct
FOR		INSERT,
		UPDATE
AS

SET NOCOUNT ON;

IF UPDATE(Prod_c_Family) OR UPDATE(Prod_Name) OR UPDATE(Prod_c_Type) OR UPDATE(Prod_c_Height) OR UPDATE(Prod_c_PotSize) OR UPDATE(Prod_c_PlantOrPot)
	UPDATE		np
	SET		np.Prod_c_Description =	i.Prod_c_Family + ' ' + i.Prod_Name + ' ' + i.Prod_c_Type
						+	CASE Prod_c_PlantOrPot
								WHEN 'Plant' THEN ' H' + CAST(i.Prod_c_Height AS NVARCHAR(20) + ' P' + i.Prod_c_PotSize
								ELSE ' H' + CAST(i.Prod_c_Height AS NVARCHAR(20) + ' W' + CAST(i.Prod_c_Width AS NVARCHAR(20) + ' D' + CAST(i.Prod_c_Depth AS NVARCHAR(20)
							END
	FROM		dbo.NewProduct AS np
	INNER JOIN	inserted AS i ON i.Prod_ProductID = l.Prod_ProductID;
GO

But you really don't need a trigger for this. Use a calculated column and make it persisted if needed.

ALTER TABLE dbo.NewProduct DROP COLUMN Prod_c_Description;
ALTER TABLE dbo.NewProduct ADD Prod_c_Description AS ISNULL(i.Prod_c_Family + ' ' + i.Prod_Name + ' ' + i.Prod_c_Type + ' H' + CAST(i.Prod_c_Height AS NVARCHAR(20)) + CASE WHEN Prod_c_PlantOrPot = 'Plant' THEN ' P' + i.Prod_c_PotSize ELSE ' W' + CAST(i.Prod_c_Width AS NVARCHAR(20)) + ' D' + CAST(i.Prod_c_Depth AS NVARCHAR(20)) END, '') PERSISTED;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources