Need your scan through the Trigger codes please.

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)

Need your scan through the Trigger codes please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-04 : 13:53:22

[code]Hi all,

I have a trigger below and I like to update OrderCode by concatenating both OrderId + CustomerCode.
Can you please check if the trigger is looking fine?. I haven't used trigger for a while and I just
need SQL Guru's to have a quick scan through the codes below. I am using SQL 2008

Thank you in advance.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO

CREATE TABLE [dbo].[Order](
[OrderId] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] [char](3) NOT NULL,
[OrderName] [varchar](60) NULL,
[OrderCode] [varchar](60) NULL,
CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Trigger.

DROP TRIGGER utr_Order;
GO
CREATE TRIGGER utr_Order
ON [Order]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON

UPDATE a
SET OrderCode = RTRIM(CAST(a.OrderId AS VARCHAR(30))) + ' ' + a.CustomerCode
FROM [Order] AS a
JOIN INSERTED AS i
ON a.OrderId = i.OrderId
AND a.CustomerCode = i.CustomerCode
GO

--------------------------------------------------------------------------------

SET IDENTITY_INSERT [dbo].[Order] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[Order]([OrderId], [CustomerCode], [OrderName])
SELECT 1, N'AB ', N'Bank of America order' UNION ALL
SELECT 2, N'CB ', N'Chase Bank order'
COMMIT;
RAISERROR (N'[dbo].[Order]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[Order] OFF;

-- It should update the OrderCode by adding (OrderId + CustomerCode).

SELECT *
FROM [Order];
go

OrderId CustomerCode OrderName OrderCode
----------- ------------ ------------------------------------------------------------ ------------
1 AB Bank of America order 1 AB
2 CB Chase Bank order 2 CB [/code]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-04 : 15:20:08

There is a lot of things wrong here. Singular table names, no real key, materializing a computed column. Put this concatenation in a computed column and not a TRIGGER.

What is an order name? Sixty characters long? I cannot imagine calling an order from Amazon.com "Fred" :) We have a customer code, but no customer identifier. Do you know the differences in _code, _type, _id and the other attribute properteis from ISO-11179?

Want to try again with valid DDL and better specs?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-04 : 16:59:57

I am just creating a test table here. In real life, there is not simple query like I posted. Just create for testing...
I wonder if you have had a real job by asking such a silly ????

quote:
Originally posted by jcelko

There is a lot of things wrong here. Singular table names, no real key, materializing a computed column. Put this concatenation in a computed column and not a TRIGGER.

What is an order name? Sixty characters long? I cannot imagine calling an order from Amazon.com "Fred" :) We have a customer code, but no customer identifier. Do you know the differences in _code, _type, _id and the other attribute properteis from ISO-11179?

Want to try again with valid DDL and better specs?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 17:34:07

"I wonder if you have had a real job by asking such a silly ????"

Quite.

But I do agree with Joe about the creation of OrderCode just being the concatenation of two columns, although I wouldn't use the computed column he recommends as over the years computed columns have caused more trouble than they were worth (I would use a VIEW for that purpose instead, as it comes without the same baggage).

But ...

your trigger will set OrderCode (to the result of concatenating OrderId and CustomerCode) regardless, but only if CustomerCode is NOT NULL.

It would be more normal to make the JOIN (between [Order] and INSERTED) using just the PK [OrderId]. For example, if [CustomerCode] was changed to become NULL then the previous value of [OrderCode] would remain unchanged.

Personally I would include a WHERE clause to only make the UPDATE if the value had changed by adding:


WHERE COALESCE(OrderCode, '') <> COALESCE(RTRIM(CAST(a.OrderId AS VARCHAR(30))) + ' ' + a.CustomerCode, '')

There is also no need to RTRIM the expression "CAST(a.OrderId AS VARCHAR(30))" - there won't be any trailing spaces.

VARCHAR(30) is also rather large for an INT - but that won't make any difference to the outcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources