SQL服务器触发的帮助 - 同表更新(sql server trigger help - same

我刚才发布了类似的问题 - 但似乎无法得到足够长的响应！ 很抱歉，如果我不应该再次发布！

这是使用SQL Server 2008。我有一个叫做发票表;

create table INVOICE(
INVOICE_ID numeric(5) PRIMARY KEY IDENTITY,
INVOICE_STATUS varchar(25) not null,
TRADER_STATUS varchar (25))

我想创建输入插入时触发; 和invoice_status =“挂起” - 的trader_status被更新（触发），以“打开”。 当输入的插入; 和invoice_status =“逾期” - 的trader_status被更新（触发）为“阻断”。

触发代码我有;

CREATE TRIGGER [dbo].[test] on [dbo].[invoice]
FOR INSERT, UPDATE
AS 
BEGIN

SET NOCOUNT ON;
declare @invoice_status varchar(25)
select @invoice_status = (select invoice_status from  inserted WHERE invoice_status = 'PENDING')
insert into invoice (trader_status) values ('OPEN')

select @invoice_status = (select invoice_status from  inserted WHERE invoice_status = 'OVERDUE')
insert into invoice (trader_status) values ('BLOCKED')

 END
 GO

任何帮助是极大的赞赏！

Answer 1:

create trigger [dbo].[test] on [dbo].[invoice]
for insert
as
begin

update Invoice
  set Trader_Status = 'OPEN'
  where Invoice_Id in ( select Invoice_Id from inserted where Invoice_Status = 'PENDING' )

update Invoice
  set Trader_Status = 'BLOCKED'
  where Invoice_Id in ( select Invoice_Id from inserted where Invoice_Status = 'OVERDUE' )

end

请注意，这将处理由单个语句插入多行。

文章来源: sql server trigger help - same table update