need help with trigger sql server 2005

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 sql server 2005

Author

Topic

bob123
Starting Member

16 Posts

Posted - 2006-10-16 : 19:10:54

I am trying to create this trigger

CREATE TRIGGER User_Insert ON aspnet_users AFTER UPDATE AS
IF UPDATE (UserId)
insert into users (aspnet_user_id, username)
select au.userid, au.username
from aspnet_users au
where au.userid != users.aspnet_user_id;

Whenever the aspnet_users table has a new user added I want the Userid and username fields from that table inserted into the
the aspnet_user_id and username fileds of the users table.

when creating the trigger I get the following error..

Msg 4104, Level 16, State 1, Procedure User_Insert, Line 3
The multi-part identifier "users.aspnet_user_id" could not be bound.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-16 : 19:23:47

First, you'll need to use the trigger table and not the actual table:

insert into users (aspnet_user_id, username)
select userid, username
from inserted

Second, I think you want an INSERT trigger and not an AFTER UPDATE trigger.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-16 : 19:27:23

You didn't name table USERS in you select statement. You need to join to table USERS.

However, this trigger will not do what you want even if it was correcly written. You stated you want to add info to the users table whenever a rows is added to the aspnet_users table. Rows are added to the aspnet_users with an insert statement and this trigger will execute only on an update.

It would be easier to do this in the stored procedure that you are using to insert the row into table aspnet_users and not use a trigger.

CODO ERGO SUM

bob123
Starting Member

16 Posts

Posted - 2006-10-16 : 21:03:33

Thank you for your replies, my trigger now looks like this

CREATE TRIGGER User_Insert ON aspnet_users FOR INSERT
AS
insert into users (aspnet_user_id, username)
select au.userid,au.username
from aspnet_users au, users
where au.userid != users.aspnet_user_id

and compiled succesfully

my question now is how do I attach it to the table?

bob123
Starting Member

16 Posts

Posted - 2006-10-16 : 21:28:38

I see the trigger is attached to the table or at least in the trigger folder but it does not work? after creating a new user in the aspnet_user table the userid and user name where not inserted into the users table.

raghuramgupta
Starting Member

3 Posts

Posted - 2006-10-17 : 03:49:46

Hi,

CREATE TRIGGER User_Insert ON aspnet_users FOR INSERT
AS
insert into users (aspnet_user_id, username)
select userid, username
from Inserted

I think this might be the code that you need, Hope this will work

Raghuram Gupta.Sarabu
GGK Tech
Hyderabad
India

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 10:39:38

I guess he didn't see my post.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources