CREATE TRIGGER ycqkcl
ON dbo.swxx
FOR INSERT
AS
BEGIN
declare @sfzh varchar
SELECT @sfzh =s_sfzh FROM INSERTED
IF EXISTS(SELECT * FROM tjl WHERE sfzh=@sfzh)
INSERT INTO ycqk (y_wid,y_sid,y_sfzh) select s_wid,s_id,s_sfzh from inserted
END
好象 @sfzh传输没法传入,大家帮我看看错误在哪里?我把SELECT * FROM tjl WHERE sfzh=@sfzh 中的@sfzh 改为常量就可以正确判断。
ON dbo.swxx
FOR INSERT
AS
BEGIN
declare @sfzh varchar
SELECT @sfzh =s_sfzh FROM INSERTED
IF EXISTS(SELECT * FROM tjl WHERE sfzh=@sfzh)
INSERT INTO ycqk (y_wid,y_sid,y_sfzh) select s_wid,s_id,s_sfzh from inserted
END
好象 @sfzh传输没法传入,大家帮我看看错误在哪里?我把SELECT * FROM tjl WHERE sfzh=@sfzh 中的@sfzh 改为常量就可以正确判断。
CREATE TRIGGER ycqkcl
ON dbo.swxx
FOR INSERT
AS
BEGIN
declare @sfzh varchar
set @sfzh=''
SELECT @sfzh =s_sfzh FROM INSERTED
IF EXISTS(SELECT * FROM tjl WHERE sfzh=@sfzh)
INSERT INTO ycqk (y_wid,y_sid,y_sfzh) select s_wid,s_id,s_sfzh from inserted
END
ON dbo.swxx
FOR INSERT
AS
BEGIN
declare @sfzh varchar(100) -- varchar默认是varchar(1)长度不够
SELECT @sfzh =s_sfzh FROM INSERTED
IF EXISTS(SELECT * FROM tjl WHERE sfzh=@sfzh)
INSERT INTO ycqk (y_wid,y_sid,y_sfzh)
select s_wid,s_id,s_sfzh from inserted
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- ============================================
-- Author: xujinli
-- Create date: 2011-12-6
-- Description: 修改的触发器(如果子表有的话就修改数据,如果没有的话就添加一个新的数据)
-- ============================================alter TRIGGER UpdateLo
ON dbo.UserInfo
for update,insert
AS
BEGIN
begin transaction /*开始事务*/
if not exists(select * from dbo.UserLogin,inserted where inserted.id=UserLogin.userid)
begin
insert into UserLogin (LoginName,Password,userid) select LoginName,Password,id from inserted
end
else
begin
declare @temp nvarchar(50)/*声明一个参数*/
set @temp=(select id from inserted)/*利用这一步可以指定对userid进行定位,但这里虽然声明了,但是没有进行操作*/
update dbo.UserLogin set LoginName=b.LoginName ,Password=b.Password from dbo.UserLogin a, inserted b
where a.userid=b.id
end commit transaction /*提交事务*/
END
GO