Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
antonio@compuconsult.net
Starting Member
8 Posts |
 Posted - 2003-10-20 : 22:58:55
|
| Hello All,Ok I have a tricky one. I have a tblOrders table, a tblOrderItems table, an tblInvoice table and an tblInvoiceItems table.Here is what I want to do. When the status of tblOrders changes to 'Activate' I want to create an Invoice for this order in tblInvoices. I also want to copy the items from tblOrderItems for this order to tblInvoiceItems. Why? Well the initial order consists of an equipment line item, and a service line item. The first invoice this order generates shall include the equipment and the service, and then the service will be monthly recurring. So I only want to create invoices for just the service line item.So what I did is create a trigger on tblOrder, checking for the status. When it detects the status I do get it to create the invoice by a stored procedure as follows:INSERT INTO tblInvoices (OrderID, InvoiceDate, ShipDate, Recurring)SELECT OrderID, ActivationDate, OrderDate, RecurringFROM tblOrdersWHERE OrderID = @OrderIDGOBut then when I try to add the items it fails, unless there is only 1 item in the OrderItems table.Any Ideas?Tony |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-21 : 12:42:21
|
| When you use a variable, it can only hold one value at a time.You didn't post your trigger code. It would also help if you posted DDL for your tables and DML for sample data so that we can easily figure out where the problem is.But something like this might be what you need in your trigger:INSERT INTO tblInvoices (OrderID, InvoiceData, ShipDate, Recurring)SELECT OrderID, ActivationDate, OrderDate, Recurring FROM INSERTEDWHERE status = 'Activate'Tara |
 |
|
|
|
|
|
|
|
Trigger and Insert From