April 4, 2008 at 12:29 am
Ok, this CAN'T be that difficult. I have been looking at a bunch of examples on the net and reading through the SQL Books Online and am just not getting it.
All I want to do is create (what I think) is a VERY basic trigger that does the following.
When a new user is created in the USERS table, the UID is inserted in to table2, that's it.
I only want the UID from the 'just added' record to be updated in to table2, not any of the other existing UID/records that previously existed.
Can someone please help me get going on this...
Thanks,
Bob
April 4, 2008 at 12:46 am
What are you struggling with?
Does this do what you want?
CREATE TRIGGER trgCopyNewUsers
ON Users -- Table the trigger's on
FOR INSERT -- What operation it triggers on
AS
Insert into Table2 (UID)
SELECT UID
FROM inserted -- the pseudo table that contains the newly inserted records.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2008 at 9:52 am
As usual, I was WAY over complicating things based on information found in the SQL books online.
Thank you VERY much!
Bob
April 4, 2008 at 1:16 pm
Still missing something.
In trying to use the following...
CREATE TRIGGER trgInsertNewUser
ON users
FOR INSERT
AS Insert into attendance
SELECT UID
FROM inserted
When executing this, assuming it will just create the trigger for the table, I am getting the following error.
Msg 213, Level 16, State 1, Procedure trgInsertNewUser, Line 5
Insert Error: Column name or number of supplied values does not match table definition.
I'm guessing I'm getting this because I am only trying to insert the UID and the attendance table has 4 columns in it. My goal was to insert just the UID and have the other 4 fields take on the default value that has been defined for those columns.
April 4, 2008 at 1:53 pm
Ug, finally figured it out.
Thanks all!
Bob
April 6, 2008 at 11:57 pm
It's good practice to always specify the column names in an insert statement. I have modified my earlier post to reflect that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply