I'm using SQL Server 2005, trying to create a conditional trigger that will fire when one of 2 status codes are entered. Once they are entered, I need it to get all the records from the PrintItems table that have the same job number and also have an MCC code and generate records in a 3rd table based on those. I'm not sure if I should be using the WHILE statement or some other method to search the PrintItems table or if I've got the whole thing muddled up. The @ItemImps formula has not yet been determined.
Thanks in advance for any advice or help!
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Trigger_DigiCost]
ON [dbo].[JobStatus1]
AFTER INSERT
AS
IF Inserted.StatusCode in (57098,60599)
BEGIN
WHILE CT_PrintItems.JobN=Inserted.JobN AND CT_PrintItems.RunMCCN > 0
BEGIN
SET NOCOUNT ON;
DECLARE @JOBN INT
DECLARE @RunMCC INT
DECLARE @ItemImps INT
DECLARE @AICRate DECIMAL
DECLARE @overs DECIMAL
DECLARE @copies INT
SELECT @JOBN=Inserted.JobN, @RunMCC=CT_PrintItems.RunMCCN, @overs=((CT_PrintItems.Qty * OpenJob.OversAllowed)/100), @ItemImps='TBD', @AICRate=MatlCostCntr.AICRate
FROM Inserted
INNER JOIN OpenJob ON OpenJob.JobN=Inserted.JobN
INNER JOIN CT_PrintItems ON CT_PrintItems.JobN=Inserted.JobN
INNER JOIN MatlCostCntr ON MatlCostCntr.MccN=CT_PrintItems.RunMCCN
INSERT INTO dbo.JobMaterial(JobMaterial.JobN, JobMaterial.MccN, JobMaterial.MatlQuantity, JobMaterial.Employee, JobMaterial.Shift,
JobMaterial.TransDate, JobMaterial.TransTime, JobMaterial.Unit, JobMaterial.AICCost, JobMaterial.UpdateDate, JobMaterial.LastChanged,
JobMaterial.ChangedBy)
VALUES (@JOBN, @RunMCC, @ItemImps, 999, 1, GetDate(), CURRENT_TIMESTAMP, @AICRate, (@ItemImps * @AICRate), GetDate(), GetDate(), 'digicost')
END
END
GOThanks in advance for any advice or help!
