Complete Newb trying learn.
I want to have Table "Sales" populate Table "Finance" when the "OrderDate" is updated/entered however I am having trouble inserting multiple columns.
CREATE TRIGGER SalesOrderDateTrigger
ON [dbo].[Sales]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderDate Date
SELECT @OrderDate = INSERTED.OrderDate FROM INSERTED
IF @OrderDate > 0
BEGIN
INSERT INTO Finance
(Quote, Customer, Project_Name, [Value],
POC_Name_#1, POC_Number_#1, POC_Email_#1,
POC_Name_#2, POC_Number_#2, POC_Email_#2,
Comment, [DA Link])
SELECT (INSERTED.Quote, INSERTED.Customer, INSERTED.Project_Name,
INSERTED.[Value],
INSERTED.POC_Name_#1, INSERTED.POC_Number_#1, INSERTED.POC_Email_#1,
INSERTED.POC_Name_#2, INSERTED.POC_Number_#2, INSERTED.POC_Email_#2,
INSERTED.Comment, INSERTED.[DA Link])
FROM INSERTED
END
END
Thank you for reading.
There are a few problems with your approach..
You're not always looking at this in a "block of data" kind of way. Sure you may only insert one row at a time, but it's actually possible to insert multiple rows into a table at the same time. When this occurs, the inserted pseudotable will have more than one row, hence you cannot select a sole order date from it, into a single variable @orderdate. Avoid any kind of "row by row" thinking with sqlserver triggers; the design intention of sqlserver server-side programming is that it is always set based; anything you write in a trigger should handle anything from zero to millions of rows all at once, not a row at a time
I'm not really sure what the line where you compare the date with 0, is trying to achieve
You subsequently run an insert that effectively inserts data that is in the Order table, into the Finance table. No new or different data is calculated, or brought in from elsewhere, which leads me to believe that order and finance share a large number of columns, having identical data. In terms of database design, this is a poor normalisation strategy; if the finance table replicates the orders table, it probably shouldn't exist. Indeed if your business logic is that an entry in the Orders table with a non null OrderDate realises an entry in the finance table, really Finance could actually just be a VIEW based off of the simple query
SELECT blahblah FROM orders WHERE orderdate IS NOT NULL