Need help with Trigger - 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

Need help with Trigger

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-18 : 07:52:13

Hi Folks,

I'm trying to create a trigger that will stop inserts into the Customers table and then post to the Customers2 table. The syntax below seems to work up to the point of rollback. Also, I would like to have a column in the Customers2 table to see the user logon details. Is there a way to have the user details automatically populate into the Customers2 table?

Create Trigger TriggerTest
On dbo.Customers
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Rollback
INSERT dbo.Customers2
Select CustomerID, Firstname, Lastname, Address1, Address2, City From Inserted
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 09:42:58

Try this:

CREATE TRIGGER TriggerTest ON dbo.Customers
AFTER INSERT AS
SET NOCOUNT ON;
BEGIN
	ROLLBACK TRAN
	INSERT dbo.Customers2(CustomerID, Firstname, Lastname, Address1, Address2, City, UserName)
	SELECT CustomerID, Firstname, Lastname, Address1, Address2, City, SUSER_SNAME() FROM Inserted;
END

Since you're skipping the INSERT entirely, I'd recommend rewriting this as an INSTEAD OF trigger, and take the ROLLBACK out. Rolling back a large INSERT is expensive and a waste in this particular scenario.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources