34,594
社区成员
发帖
与我相关
我的任务
分享
create trigger tri_a_insert on a
after insert
as
update 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不可为空 */
--测试数据
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