对增、删、改最好分别写一触发器,简单写个例子(语法可能不完全正确)
CREATE TRIGGER tr_name FOR INSERT ON Member
DECLARE s char(2);
BEGIN
IF inserted.sex = 1 then
select @s = '男';
ELSE
select @s = '女';
end if
INSERT INTO [用户]([姓名], [密码], [性别])
VALUES(inserted.username, inserted.password, @s);
END
CREATE TRIGGER tr_name FOR INSERT ON Member
DECLARE s char(2);
BEGIN
IF inserted.sex = 1 then
select @s = '男';
ELSE
select @s = '女';
end if
INSERT INTO [用户]([姓名], [密码], [性别])
VALUES(inserted.username, inserted.password, @s);
END
删除和修改的触发器怎样写呀
特别是修改触发器,updated
BEGIN
DELETE FROM [用户] WHERE [姓名]=deleted.username
END;CREATE TRIGGER tr_name_update FOR UPDATE ON Member
BEGIN
-- 你是用username与用户做主键吧
UPDATE [用户] SET [姓名]=inserted.username,
[密码]=inserted.password,
[性别]= --同插入一样处理了,我赖得写了
WHERE [用户]=deleted.username
END;
我觉得你的用户表根本没必要存在。用一个view就可以解决问题。create view 用户
as
select username as 姓名,password as 密码,
性别=(case sex when 1 then N'男'
when 2 then N'女'
end)
from member
你的trigger!
其实你这个问题!真的是高得太差了
不说表的建立
就说字段!一样的字段为什么一个要bit类型 一个要nvarchar 类型!
WHQ()方法可以但是语法不错不完全
另外一个
可以这样
create trigger t_name on [用户] for insert,delete,update
as
declare
@s bit
begin
if (select count(*) from inserted)>0
begin
if inserted.性别="男"
@s=1
else
@s=0
insert into [member]
select 姓名,密码,@s from inserted
end
if (select count(*) from deleted)>0
delete [member] from [member],deleted where [member].username=deleted.姓名--这里假设username是关键字
end