现在有表A和表B,表A中有字段NO(主键),USERNAME,TABNAME,MESSAGE,STATUS,表B中也有USERNAME,TABNAME,这两个字段,
现在往表A中插入数据时,要判断表A的USERNAME,TABNAME字段值在表B中是否存在,如果不存在就将表A的MESSAGE值改为null,STATUS值改为-2
现在往表A中插入数据时,要判断表A的USERNAME,TABNAME字段值在表B中是否存在,如果不存在就将表A的MESSAGE值改为null,STATUS值改为-2
--测试数据
if not object_id(N'A') is null
drop table A
Go
Create table A([NO] INT PRIMARY KEY IDENTITY,[USERNAME] nvarchar(22),[TABNAME] nvarchar(21),[MESSAGE] nvarchar(22),[STATUS] nvarchar(22))
GO
if not object_id(N'B') is null
drop table B
Go
Create table B([USERNAME] nvarchar(22),[TABNAME] nvarchar(25))
Insert B
select N'张三',N'Order'
Go
--测试数据结束建立触发器:
CREATE TRIGGER dbo.A_TRI
ON [dbo].[A]
AFTER INSERT
AS
UPDATE A
SET MESSAGE = NULL,
STATUS = -1
FROM Inserted,
B
WHERE Inserted.NO = A.NO
AND Inserted.USERNAME = B.USERNAME
AND Inserted.TABNAME = B.TABNAME;
GO
测试插入数据:
INSERT INTO dbo.A
(
USERNAME,
TABNAME,
MESSAGE,
STATUS
)
VALUES
( N'张三', -- USERNAME - nvarchar(22)
N'Order', -- TABNAME - nvarchar(21)
N'测试1', -- MESSAGE - nvarchar(22)
N'1' -- STATUS - nvarchar(22)
),( N'李四', -- USERNAME - nvarchar(22)
N'Book', -- TABNAME - nvarchar(21)
N'测试2', -- MESSAGE - nvarchar(22)
N'1' -- STATUS - nvarchar(22)
)读取结果:
SELECT * FROM dbo.A
SELECT * FROM dbo.B
create trigger tri_a_insert on a
after insert
asupdate a set [MESSAGE]=null,[STATUS]=-2
from a
inner join inserted c on a.[no]=c.[no]
left join b on a.USERNAME=b.USERNAME and a.TABNAME=b.TABNAME
where b.TABNAME is null /*这种写法b表中的TABNAME不可为空 */
for insert
as update a
set message=null,status=-2
from a join inserted b on a.no=b.no
where not exists(select * from b join inserted on b.username=inserted.USERNAME and b.TABNAME=insert.TABNAME)应该是这么实现的了