How to add the column by default in sql server 2005
I have 2 tables called login
and Roles
.
In the login
table, I have these fields:
CREATE TABLE [dbo].[login]
([Id] [int] IDENTITY(1,1) NOT NULL,
[U开发者_如何学Pythonname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Pwd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_login_1] PRIMARY KEY CLUSTERED([Uname] ASC)
In the roles
table I have these fields:
CREATE TABLE [dbo].[Roles]
([Uname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Valid] [int] NOT NULL
)
Now what I need is if I fill the uname
as some xyz I would like to fill the same uname
in the role table automatically in the corresponding field that i makes as foreign key...
You could do this using a Trigger. You may or may not want to execute this code on an Insert
and / or Update
Further details on triggers can be found here
CREATE TRIGGER trgInsertUserIntoRoles ON Login
FOR Insert
AS
INSERT INTO Roles (UName, Valid)
SELECT Uname, 1
FROM Inserted
Although I think it would be better if you just added the code to insert the username into the Roles
table within the Stored Procedure to create the user.
Also, you are aware that you are creating all this on the master
database?
A solution is to put a trigger on inserts to the original table.
This microsoft article on triggers will tell you how they work.
精彩评论