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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:26:18

Simone writes "CREATE TRIGGER utr_CustomerInsert ON dbo.Customer
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @id int

SELECT @id = OrderID
FROM Customer, Order, Inserted
WHERE Inserted.ID = Customer.ID
AND Order.CustomerID = Inserted.ID

INSERT INTO LogTable
SELECT suser_sname(), getdate(), @id
END

Could you look at the trigger source code, in terms of best practice, possible errors and performance.

Thanks in advance."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-18 : 09:30:16

This line:

SELECT @id = OrderID

Will not work properly for multi-row updates, you will get the OrderID of the last row returned by the query. You're also writing JOINs in a non-ANSI style. I don't see any logic in inserting an Order ID whenever a new Customer is inserted or updated. What happens if the Customer has no orders? This renders the INSERT action of the trigger meaningless.

It would be better if you describe what you're trying to accomplish rather than just posting the code.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-18 : 09:30:49

The original does not handle multiple records in inserted table

INSERT INTO LogTable
SELECT suser_sname(), getdate(), OrderID
FROM   Customer, Order, Inserted
WHERE  Inserted.ID = Customer.ID
AND    Order.CustomerID = Inserted.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources