sql server触发器帮助-相同表更新 [英] sql server trigger help - same table update

问题描述

我早些时候发布了类似的问题-但似乎无法获得足够长的回复！抱歉，如果我不希望再次过帐！

这是使用SQL Server2008。我有一个称为发票的表；

 创建表INVOICE（
 INVOICE_ID数字（5）主键标识，
 INVOICE_STATUS varchar（25）不为空，
 TRADER_STATUS varchar（25 ））
  

我想在输入插入内容时创建触发器；并且invoice_status =待处理-trader_status已更新（触发）为打开。输入插入内容时；并且invoice_status =过期-trader_status已更新（触发）为已阻止。

我有触发代码；

 创建触发器[dbo]。在[dbo]上进行[测试]。[发票] 
用于插入，更新
 AS 
开始
 
开启NOCOUNT； 
声明@invoice_status varchar（25）
 select @invoice_status =（从插入的WHERE invoice_status ='PENDING'中选择invoice_status）
插入发票（trader_status）值（'OPEN'）
 
 select @invoice_status =（从插入的WHERE invoice_status ='OVERDUE'中选择invoice_status）
插入发票（trader_status）值（'BLOCKED'）
 
 END 
 GO 
  

任何帮助都将不胜感激！

解决方案

 创建触发器[dbo]。[test] on [dbo]。[发票] 
用于插入
，因为
开始
 
更新发票
 set Trader_Status ='OPEN'
其中Invoice_Id在（从插入的位置选择Invoice_Id Invoice_Status ='PENDING'）
 
更新Invoice 
 set Trader_Status ='BLOCKED'
其中Invoice_Id在（从插入的Invoice_Id中选择Invoice_Id = Invoice_Status ='OVERDUE'）
 
 end 
  

请注意，这将处理单个语句插入的多个行。 / p>

I posted a similar question earlier - but can't seem to get a long enough response! Sorry if I shouldn't be posting again!

This is using SQL Server 2008. I have a table called invoice;

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

I want to create a trigger when an insert is entered; and the invoice_status = 'pending' - the trader_status is updated (triggered) to 'open'. When an insert is entered; and the invoice_status = 'overdue' - the trader_status is updated (triggered) to 'blocked'.

The trigger code I have;

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

Any help is greatly appreciated!

解决方案

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

Note that this will handle more than one row being inserted by a single statement.

这篇关于sql server触发器帮助-相同表更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！