Need a stored procedure or trigger for populating removing information

Have following user table

aspnet_users table
UserID
UserName

When I create the user I need this information to auto-populate another table and the same if I delete the user

users table
UserID = aspnet table UserID
Name = aspnet table Username
UserName = aspnet table Username
JDay2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JDay2Author Commented:
How do I accomplish this with a stored procedure or tigger?
0
Anthony PerkinsCommented:
Something like this perhaps:
CREATE TRIGGER trg_LogUsers ON UsersTable AFTER INSERT, DELETE
AS

INSERT AnotherTable(Action, UserID, Name, UserName)
SELECT 'INSERT', UserID, Name, UserName
FROM	Inserted
Union ALL
SELECT 'DELETE', UserID, Name, UserName
FROM	Inserted

Open in new window

0
JDay2Author Commented:
Okay I have a few  questions and I'm going to break them into steps

I had to remove the Action column in your code because don't have that column.

USE []
GO
/****** Object:  Trigger [dbo].[trg_insertUsers]    Script Date: 3/24/2015 10:08:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_insertUsers] ON [dbo].[aspnet_Users] AFTER INSERT
AS

INSERT dbo.Users(UserID, UserName)
SELECT  UserID, UserName
FROM	Inserted

Open in new window


this works for inserting but I need to insert into a name column in the dbo.users table based of Username is this possible?

How do I implement the delete piece based of the UserID?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

JDay2Author Commented:
Got it by doing the following

USE []
GO
/****** Object:  Trigger [dbo].[trg_insertUsers]    Script Date: 3/24/2015 1:37:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_insertUsers] ON [dbo].[aspnet_Users] AFTER INSERT
AS

INSERT dbo.Users(UserID,Name, UserName)
SELECT  UserID, UserName, UserName
FROM	Inserted

Open in new window


USE []
GO
/****** Object:  Trigger [dbo].[trg_deleteUsers]    Script Date: 3/24/2015 1:38:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_deleteUsers] ON [dbo].[aspnet_Users] AFTER DELETE
AS

DELETE FROM dbo.Users 
WHERE dbo.Users.UserID= (Select UserID from deleted)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Fair enough.  I would have been more inclined to use a single TRIGGER, but whatever works for you.
0
JDay2Author Commented:
The initial post gave me the direction but I didn't have an action column in my table.
The post help understand the concept to formulate the following triggers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.