My First 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

My First Trigger

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-11-04 : 12:54:02

The title of the post pretty much says it all...

The goal is to detect when new rows are created on a table. I'm testing this on SQL Server 2000, but the real database is SQL Server 2005.

When new rows are added I need to get data from several columns out to a text file. In this test, I'm just trying to write data from 2 columns to another table in the same database. Below, it is written with 'FOR INSERT, UPDATE AS' because I eventually want to expand it to also detect changes on 4 columns. For now though, I'm just trying to deal with new rows inserted.

I created this in Query Analyzer and the syntax checks fine and it runs and creates the trigger. The trigger fires as expected, but I get an error in the INSERT INTO tblChanges. It tells me the object does not exist, but the table is there in the same database. I tried dbo.tblChanges and got the same error.

You'll also notice that I have the two column names in single quotes, otherwise the syntax checker does not like them. I did this just so I could save the trigger to see if it works.

Obviously there is a problem with "INSERT INTO tblChanges ('Routeby', 'RouteFrom') SELECT Routeby, RouteFrom FROM tblFaxRoute", but I'm not sure what it is. This also does not seem correct because I only want to get data from the row that was inserted. This INSERT statement has no WHERE clause and I'm not really sure what I should be filtering for.

Greg

USE IPAFax
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewPatient' AND type = 'TR')
DROP TRIGGER NewPatient
GO

CREATE TRIGGER NewPatient
ON tblFaxRoute
FOR INSERT, UPDATE AS
INSERT INTO tblChanges ('Routeby', 'RouteFrom') SELECT Routeby, RouteFrom FROM tblFaxRoute
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-04 : 14:11:46

Read about, and look at samples for, CREATE TRIGGER in Books Online. You'll need to SELECT from [inserted] which is a virtual table available within the scope of a trigger.

Be One with the Optimizer
TG

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-11-04 : 14:17:15

Thanks, I did read on-line books and samples. That is how I got as far as I did, but I did not see any examples that showed what I was trying to do. Perhaps I did not Google far enough.

So that would change this to:

INSERT INTO tblChanges ('Routeby', 'RouteFrom') SELECT Routeby, RouteFrom FROM [inserted]

???? I guess ????

That still doesn't explain why the INSERT SQL statement does not see tblChanges?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-04 : 14:19:53

Try this:


...
INSERT INTO dbo.tblChanges ([Routeby],[RouteFrom])
SELECT Routeby, RouteFrom FROM inserted

CODO ERGO SUM

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-11-04 : 14:31:39

Google on 'using virtual table [inserted] in a trigger' brought up this page http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=59

Using that example I still get "Invalid Column name" when I try to access data from the v-table [inserted]

SELECT @RouteBy = (SELECT RouteBy FROM Inserted)
SELECT @RouteTo = (SELECT RouteTo FROM Inserted)

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-11-04 : 14:52:22