title: sql server trigger help - same table update
body: <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!</p>
  
  <p>This is using SQL Server 2008. I have a table called invoice;</p>
  
  <pre><code>create table INVOICE(
  INVOICE_ID numeric(5) PRIMARY KEY IDENTITY,
  INVOICE_STATUS varchar(25) not null,
  TRADER_STATUS varchar (25))
  </code></pre>
  
  <p>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'. </p>
  
  <p>The trigger code I have; </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any help is greatly appreciated! </p>