CODEWITHSUNDEEP
sqlsql-server
Justin Adkins
Justin Adkins

Reputation: 1234

Automatically create record when another is created

I have two tables in my database: Users, Roles and Membership. The Membership table assigns users to specific Roles.

How could I automatically create the Membership record for anytime a new record is inserted in Users.

Example: When a user is created and assigned an ID number (# 562), The database would automatically add them to the Membership table with a specific role ID.

How could I do this?

Upvotes: 0

Views: 313

Answers (1)

Shiva
Shiva

Reputation: 20935

Write an AFTER INSERT TRIGGER on Users TABLE, that will INSERT the new Row in the Membership table.

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

Assuming you have a Default RoleID for your new Membership row, when a new User is inserted in Users table, something like this should work.

CREATE TRIGGER TRI_USERS_INSERT on Users
AFTER INSERT
AS
SET NOCOUNT ON

-- If you have a Default RoleID, select that into a variable and use it in the INSERT below.
-- For this example, I am using just the number 1
-- Also assumes that the ID for Memberships table is AUTO GENERATED, so it's not in INSERT list.
INSERT INTO Memberships (UserID, RoleID)
    SELECT ID, 1 FROM INSERTED
GO

Upvotes: 2

Related Questions