StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsql server trigger
text
Body
copied!<blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/13573352/sql-server-trigger-help-same-table-update">sql server trigger help - same table update</a> </p> </blockquote> <p>I am using Microsoft SQL Server 2008. I have two tables credit and invoice. </p> <p>The code for the credit table:</p> <pre><code>create table CREDIT( CREDIT_ID numeric(5) PRIMARY KEY IDENTITY, CREDIT_STATUS varchar (25)) </code></pre> <p>The code for the invoice table:</p> <pre><code>create table INVOICE( INVOICE_ID numeric(5) PRIMARY KEY IDENTITY, CREDIT_ID numeric(5) not null, INVOICE_STATUS varchar(25) not null, FOREIGN KEY (CREDIT_ID) REFERENCES CREDIT(CREDIT_ID)) </code></pre> <p>The insert statement(s) for credit:</p> <pre><code> insert into credit values ('open') </code></pre> <p>I want to create a trigger - when an insert statement is entered on the invoice table - if the invoice_status is 'pending' - the credit_status remains 'open'. If the invoice_status is 'overdue' - the credit_status is updated (triggered) to 'blocked'. </p> <p>The problem I'm having (i think) is based on the primary key identifiers - it doesn't know which one to update.</p> <p>The trigger code I have so far:</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 = 'overdue') insert into credit (credit_status) values ('Blocked') select @invoice_status = (select invoice_status from inserted WHERE invoice_status = 'pending') insert into credit (credit_status) values ('Open') END GO </code></pre> <p>Sorry for any confusion!</p> <p>Any help is greatly appreciated!</p> <p>Thanks! </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload