3张表 HR_Employee (人员信息表) HR_Card (卡片信息表) HR_Group (人员分组表)HR_Employee 表结构
autoID 字段:自动编号-人员编号HR_Card表结构
empID 字段:人员编号
dateTime datetime 8 0 字段:失效日期(取当前系统时间)
type int 4 0 字段:卡片类型
money money 8 0 字段:余额
state int 4 0 字段:卡片状态 int 的HR_Group 表结构
empID 字段:人员编号
==============================================================================================================
我要的结果是当插入一条人员记录后,触发 这个触发器
自动添加新的人员 autoID 去
卡片表:
empID = autoID
dateTime = 系统当前时间
type = 1
money = 0
state = 1
分组表:
empID = autoID 直接上代码,迅速揭帖。刚才问了一个字段的,2个字段的琢磨不出来,悲哀,我挺内疚的。
autoID 字段:自动编号-人员编号HR_Card表结构
empID 字段:人员编号
dateTime datetime 8 0 字段:失效日期(取当前系统时间)
type int 4 0 字段:卡片类型
money money 8 0 字段:余额
state int 4 0 字段:卡片状态 int 的HR_Group 表结构
empID 字段:人员编号
==============================================================================================================
我要的结果是当插入一条人员记录后,触发 这个触发器
自动添加新的人员 autoID 去
卡片表:
empID = autoID
dateTime = 系统当前时间
type = 1
money = 0
state = 1
分组表:
empID = autoID 直接上代码,迅速揭帖。刚才问了一个字段的,2个字段的琢磨不出来,悲哀,我挺内疚的。
CREATE TRIGGER tri_test ON HR_Employee
AFTER INSERT
AS
IF @@ROWCOUNT = 0
RETURN ; INSERT HR_Card(empID) SELECT autoID FROM inserted;
GO这是之前我得到的代码,高手,我给你拷贝过来了。
--这样?create trigger tri_emp on HR_Employee for insert
as
insert into hr_card select autoid,getdate(),1,0,1 from inserted
insert into HR_Group select autoid from inserted
go
as
begin
insert into HR_Card(empID,dateTime,type,[money],state) select autoID , getdate() , 1 , 0 , 1 from inserted
insert into HR_Group (empID) select autoID from inserted
end
for insert
as
INSERT
HR_Card(empID)
SELECT
autoID
FROM
inserted i,HR_Card b,HR_Group c
where
b.empID=c.empID
and
b.empID = i.autoID
create trigger f on HR_Employee for insert
as
insert into hr_card select autoid,getdate(),1,0,1 from inserted
insert into HR_Group select autoid from inserted
go
FOR INSERT,UPDATE,DELETE
AS
BEGIN
INSERT HR_CARD SELECT AUTOID,GETDATE(),1,0,1 FROM INSERTED
INSERT HR_GROUP SELECT AUTOID FROM INSERTED
DELETE HR_CARD WHERE EMPID IN(SELECT AUTOID FROM DELETED)
DELETE HR_GROUP WHERE AUTOID IN(SELECT AUTOID FROM DELETED)
END
GO