Create trigger to format text - 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

Create trigger to format text

Author

Topic

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-04 : 23:42:29

We are using an old version of Numara TrackIT for our helpdesk software, and it doesn't have much in the way of configurable options. There is no way to set validation or formatting on the text fields in the program.

There is a field, WO_TEXT1, Which I would like to be formatted as 6 characters, 3 integers + a period + 2 integers. The first the integers would be padded with zeros on the left, and the last 2 integers would be padded with zeros on the right.

IE, if someone enters 2, it would actually end up being 002.00
If someone enters 3.5 it would end up being 003.50
If someone enters 12.1 it would end up being 012.10
If someone enters 172.80 it would end up being 172.80

I was hoping to achieve this via an update trigger.
Below is the guts of the trigger I created, mostly as a
proof of concept.

-- This update properly formats the Estimated Hours field
Update t SET WO_TEXT1 = (SELECT RIGHT('000000' + CONVERT(VARCHAR(6), WO_TEXT1), 6) FROM inserted)
FROM dbo.TASKS as t
Where (EXISTS (SELECT * FROM inserted WHERE WOID = 24773));

I expected that this update trigger would only affect the Work Order with a WOID of 24773. Unfortunately, it updated all 21000 work orders in our system, wiping out all of the actual estimated hours that had been inserted by technicians!

Luckily I had a report that I could quick dump the 300 or so active work order's estimated hours back into the DB from (all the other Work orders are closed, and no one really cares about their estimated hours).

My question is three fold,

1) Why did my trigger update every record in the tasks table instead of just WO 24773?
2) Is using a trigger the best way of accomplishing what I'm trying to do?
3) if a trigger is the best way of accomplishing this, what should my trigger look like?

Thanks in advance for any help you can provide!

Taylor Hammerling

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 02:33:18

1. Your where clause. It says "where any row is for workorder 24773"

2. This should be handled by the application, not the db.

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-05 : 09:14:49

Gbritton - Thanks for your response!

I agree, this should be handled by the application, but the application (which I don't have access to the source code) doesn't have any options for data validation or formatting.

Can you please show me how the where clause should look to only affect WO 24773?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 09:57:24

Join inserted using the wo number

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-05 : 18:37:33

FYI, for anyone interested, I got this working using the following code


   -- This update properly formats the Estimated Hours field
   Declare @Integer as varchar(12)
   Declare @Decimal as varchar(12)
   Declare @temp as varchar(12)
   if (select CHARINDEX('.',WO_TEXT1) from inserted) = 0 set @temp = '000' + (select WO_TEXT1 from inserted)+'.00'
   else set @temp = '000'+(select WO_TEXT1 from inserted)+'00'
   set @Integer = right(left(@temp, charindex('.', @temp)-1),3)
   set @Decimal = left(right(@temp, len(@temp)-charindex('.', @temp)),2)
   set @temp = @Integer + '.' + @Decimal
  
   Update t SET WO_TEXT1 = @temp 
   FROM dbo.TASKS as t
   Where (EXISTS (SELECT * FROM inserted WHERE WOID = t.woid)) AND (WO_TEXT1 is not NULL);

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 21:04:52

your where clause is incorrect. you are basically saying:

update all rows in dbo.tasks if there is any row in inserted where tasks.woid = inserted.woid and wo_text1 is not null.

You should write:

update t set wo_text1...
from dbo.tasks as t
join inserted
on t.woid = inserted.woid
where wo_text1 is not null

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 21:04:52

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-06 : 09:14:09

Interesting... I wonder why the way I coded it is working *lol*.
That's the way I coded all of my update triggers, and they all are updating only the record in the table that was changed...

I will play with the way you wrote the update statement in my development environment. If nothing else, I feel it reads more
clearly than the way I was doing it...

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-06 : 15:54:09

Ok, so this is what I'm using thanks to gbritton's help


USE [TrackIT9_DEV]
GO
/****** Object:  Trigger [dbo].[DEV$TasksUpdate]    Script Date: 03/06/2015 12:52:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Taylor Hammerling
-- Create date: 2015-03-05
-- Description:	test trigger
-- =============================================
ALTER TRIGGER [dbo].[DEV$TasksUpdate] 
   ON  [dbo].[TASKS] 
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   -- This update properly formats the Estimated Hours field
   Declare @Integer as varchar(12)
   Declare @Decimal as varchar(12)
   Declare @temp as varchar(12)
   if (select CHARINDEX('.',WO_TEXT1) from inserted) = 0 set @temp = '000' + (select WO_TEXT1 from inserted)+'.00'
   else set @temp = '000'+(select WO_TEXT1 from inserted)+'00'
   set @Integer = right(left(@temp, charindex('.', @temp)-1),3)
   set @Decimal = left(right(@temp, len(@temp)-charindex('.', @temp)),2)
   set @temp = @Integer + '.' + @Decimal
  
   Update t SET WO_TEXT1 = @temp 
   FROM dbo.TASKS as t
   Join inserted
   on t.WOID = inserted.WOID
   Where (inserted.WO_TEXT1 is not NULL);

    -- Insert statements for trigger here

END

The only problem is, if you attempt to perform a multi-row update on TASKS, IE


update [TRACKIT9_DEV].dbo.tasks
Set SessionId = Null
Where SessionId = 247441

(sessionId is used to track which web session has a Work order open, therefore it is very likely that multiple rows will have the same sessionId)

You get the following error


Msg 512, Level 16, State 1, Procedure DEV$TasksUpdate, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I know I read somewhere on how to write triggers so they can handle multi-row updates, but I can't find the page again for the life of me... Any thoughts? :)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-07 : 05:19:53

Sure. Look at the subquery in the if stmt. The if stmt expects one value. You get this error if multiple rows inserted.

RW-Taylor
Starting Member

6 Posts

Posted - 2015-03-10 : 11:15:14

I worked out the code below, (through some googling) which works, however if you try to do an update that is to big (like say, mass updating all 21000 records in the tasks table) It hangs the SQL server...
Any suggestions on a better way to rewrite this trigger to handle multi-row inserts/updates?


-- This update properly formats the Estimated Hours field
   Declare @Integer as varchar(12)
   Declare @Decimal as varchar(12)
   Declare @temp as varchar(12)
   Declare @WO_TEXT_TEMP as varchar(6)
   
   DECLARE MY_CURSOR CURSOR 
		LOCAL STATIC READ_ONLY FORWARD_ONLY
   FOR 
   SELECT WO_TEXT1 
   FROM inserted 
   Open MY_CURSOR
   FETCH NEXT FROM MY_CURSOR INTO @WO_TEXT_TEMP
   WHILE @@FETCH_STATUS = 0
   BEGIN
	if (CHARINDEX('.',@WO_TEXT_TEMP)) = 0 set @temp = '000'+ (@WO_TEXT_TEMP)+'.00'
	else set @temp = '000'+(@WO_TEXT_TEMP)+'00'
	set @Integer = right(left(@temp, charindex('.', @temp)-1),3)
	set @Decimal = left(right(@temp, len(@temp)-charindex('.', @temp)),2)
	set @temp = @Integer + '.' + @Decimal
  
	Update t SET WO_TEXT1 = @temp 
	FROM dbo.TASKS as t
	Join inserted
	on t.WOID = inserted.WOID
	Where (inserted.WO_TEXT1 is not NULL);
	FETCH NEXT FROM MY_CURSOR INTO @WO_TEXT_TEMP
   End
   CLOSE MY_CURSOR
   DEALLOCATE MY_CURSOR

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-10 : 11:44:22

don't use a cursor (in fact, don't ever use them!). Here's a version (I can't test it of course, since I don't have your data) that does it without a cursor:


IF @@rowcount = 0 RETURN;

UPDATE t
  SET t.WO_TEXT1 = wo.text1
FROM dbo.TASKS AS t
JOIN inserted
    ON t.WOID = inserted.WOID

CROSS APPLY(
	SELECT CASE CHARINDEX('.', inserted.WO_TEXT1)
                        WHEN 0
                            THEN '000' + inserted.WO_TEXT1 + '.00'
                        ELSE '000' + inserted.WO_TEXT1 + '00'
                    END
) temp(temp)

CROSS APPLY(
	SELECT temp = RIGHT(LEFT(temp, CHARINDEX('.', temp) - 1), 3) + '.' + LEFT(RIGHT(temp, LEN(temp) - CHARINDEX('.', temp)), 2)
) wo(text1);

Note that this could probably be simplified further. I kept your intermediate calculations so you can see how do do this without a cursor.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 11:58:37

[code]CREATE TRIGGER dbo.trgFormatText
ON dbo.Tasks
AFTER INSERT,
DELETE
AS

SET NOCOUNT ON;

UPDATE t
SET t.Wo_Text1 = REPLACE(STR(i.Wo_Text1, 6, 2), ' ', '0')
FROM dbo.Tasks AS t
INNER JOIN inserted AS i ON i.WoID = t.WoID[/code]Assuming WoID is unique or primary key on dbo.Tasks table.

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