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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.