先说明下面代码的用途
假如这是一个注册的页面 得到的信息将保存到两张表中, MAccount ,MPass 保存到MainInfo中 ,其它的信息保存到ChildInfo中,请问这个用触发器怎么做?小弟初接触触发器希望能详细点。还有能解释下触发器中的Inserted 和 Deleted表吗?USE Test
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='MainIfo')
DROP TABLE MainInfo
CREATE TABLE MainInfo --创建一个主表
(
MId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
MAccount NVARCHAR(10) NOT NULL ,
MPass NVARCHAR(20) NOT NULL
)IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='ChildInfo')
DROP TABLE ChildInfo
CREATE TABLE ChildInfo --子信息表
(
CId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
MId INT NOT NULL,
NickName NVARCHAR(20) NOT NULL,
Age INT CHECK(Age>0 AND Age<150),
Gender CHAR(2) DEFAULT('男'),
Avatar NVARCHAR(50),
Hometowm NVARCHAR(50)
)
--创建主外键关系
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='FK_MainInfo_ChildInfo')
ALTER TABLE ChildInfo
DROP CONSTRAINT FK_MainInfo_ChildInfoALTER TABLE ChildInfo
ADD CONSTRAINT FK_MainInfo_ChildInfo
FOREIGN KEY
(MId) REFERENCES MainInfo(MId)
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME='T_INSERT')
DROP TRIGGER T_INSERT
--创建INSERT 触发器
CREATE TRIGGER T_INSERT
ON ChildInfo
FOR INSERT
AS
BEGIN TRANSACTION
--下面应该怎么写???--求高手指教
假如这是一个注册的页面 得到的信息将保存到两张表中, MAccount ,MPass 保存到MainInfo中 ,其它的信息保存到ChildInfo中,请问这个用触发器怎么做?小弟初接触触发器希望能详细点。还有能解释下触发器中的Inserted 和 Deleted表吗?USE Test
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='MainIfo')
DROP TABLE MainInfo
CREATE TABLE MainInfo --创建一个主表
(
MId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
MAccount NVARCHAR(10) NOT NULL ,
MPass NVARCHAR(20) NOT NULL
)IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='ChildInfo')
DROP TABLE ChildInfo
CREATE TABLE ChildInfo --子信息表
(
CId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
MId INT NOT NULL,
NickName NVARCHAR(20) NOT NULL,
Age INT CHECK(Age>0 AND Age<150),
Gender CHAR(2) DEFAULT('男'),
Avatar NVARCHAR(50),
Hometowm NVARCHAR(50)
)
--创建主外键关系
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='FK_MainInfo_ChildInfo')
ALTER TABLE ChildInfo
DROP CONSTRAINT FK_MainInfo_ChildInfoALTER TABLE ChildInfo
ADD CONSTRAINT FK_MainInfo_ChildInfo
FOREIGN KEY
(MId) REFERENCES MainInfo(MId)
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME='T_INSERT')
DROP TRIGGER T_INSERT
--创建INSERT 触发器
CREATE TRIGGER T_INSERT
ON ChildInfo
FOR INSERT
AS
BEGIN TRANSACTION
--下面应该怎么写???--求高手指教
insert into MainInfo(MAccount,MPass) select MAccount,MPass from ...insert into ChildInfo (其他字段) select 其他字段 from ...