Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Only want the trigger to fire the first time 1 of 2 codes are entered

Status
Not open for further replies.
LMGroup

LMGroup

MIS
Apr 10, 2006
85
CA
I'm using SQL Server 2005. I got a lot of help on here a while ago getting this set up and it works perfectly except when someone enters one of the codes after one has already been used. They aren't supposed to, but it happens. I need to prevent this because it is doubling (or tripling) the costs on the job. I started out trying to use a subquery that counted the number of times the codes were used but got hung up. I thought a CTE would work but I'm not sure where to place it in the trigger.

This is the current trigger.
Code: 
ALTER TRIGGER [dbo].[LM_DigiCost]
   ON  [dbo].[JobStatus1] 
   AFTER INSERT
AS 
BEGIN
  INSERT 
  INTO    dbo.JobMaterial(
              JobN, 
              MccN, 
              MatlQuantity, 
              Employee, 
              Shift, 
              TransDate, 
              TransTime, 
              Unit, 
              AICCost, 
              UpdateDate, 
              LastChanged, 
              ChangedBy)
  SELECT   Inserted.JobN, 
           CT_PrintItems.RunMCCN, 
           (CASE WHEN CT_PrintItems.PgRec > 0 THEN
				CASE WHEN (((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
					CT_PrintItems.Pages/2
				ELSE
					CT_PrintItems.Pages
				END) + CT_PrintItems.Setup) % CT_PrintItems.Up > 0) THEN
					(FLOOR(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
								CT_PrintItems.Pages/2
							ELSE
								CT_PrintItems.Pages
							END) + CT_PrintItems.Setup)/CT_PrintItems.Up) + 1) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
									CT_PrintItems.Qty
								WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
									CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
										FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
									ELSE
										FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
									END
								END) * CT_PrintItems.PgRec
							ELSE
								(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
									CT_PrintItems.Pages/2
								ELSE
									CT_PrintItems.Pages
								END) + CT_PrintItems.Setup)/CT_PrintItems.Up) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
										CT_PrintItems.Qty
									WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
										CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
										ELSE
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
										END
									END) * CT_PrintItems.PgRec
								END
								ELSE
									(CASE WHEN CT_PrintItems.PgRec > 0 THEN
										CT_PrintItems.Qty
									WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
										CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
										ELSE
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
										END
									END) * (CASE WHEN CT_PrintItems.Format = 'Signature' THEN
											CT_PrintItems.Pages/2
										ELSE
											CT_PrintItems.Pages
										END)
									END),
           999,
           1,
           Inserted.UpdateDate,
           Cast(Replace(Convert(varchar(5),GetDate(),108),':','') as int),
           MatlCostCntr.AICRate,
           MatlCostCntr.AICRate * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
									CASE WHEN (((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
										CT_PrintItems.Pages/2
									ELSE
										CT_PrintItems.Pages
									END) + CT_PrintItems.Setup) % CT_PrintItems.Up > 0) THEN
									(FLOOR(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
									CT_PrintItems.Pages/2
								ELSE
									CT_PrintItems.Pages
								END) + CT_PrintItems.Setup)/CT_PrintItems.Up) + 1) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
										CT_PrintItems.Qty
									WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
										CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
										ELSE
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
										END
									END) * CT_PrintItems.PgRec
								ELSE
									(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
										CT_PrintItems.Pages/2
									ELSE
										CT_PrintItems.Pages
									END) + CT_PrintItems.Setup)/CT_PrintItems.Up) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
											CT_PrintItems.Qty
										WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
											CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
												FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
											ELSE
												FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
											END
										END) * CT_PrintItems.PgRec
									END
								ELSE
									(CASE WHEN CT_PrintItems.PgRec > 0 THEN
										CT_PrintItems.Qty
									WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
										CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
										ELSE
											FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
										END
									END) * (CASE WHEN CT_PrintItems.Format = 'Signature' THEN
											CT_PrintItems.Pages/2
										ELSE
											CT_PrintItems.Pages
										END)
									END), 
           Inserted.UpdateDate, 
           GetDate(), 
           'digicost'
  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
  WHERE    Inserted.StatusCode In (60599,96904)
           And CT_PrintItems.RunMCCN > 0
END

This small CTE test isolates what I need but I don't know if it will work in here:

Code: 
WITH SCCount_CTE 
AS
(
SELECT JobN 
FROM  (SELECT JobN,COUNT(StatusCode) C
	FROM JobStatus1 
	WHERE StatusCode in (60599,96904)
	GROUP BY JobN,StatusCode) T
	WHERE C <= 1 
)
 
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
385
Chris Miller
Chris Miller
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
454
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
172
grnzbra
grnzbra
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
149
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
356
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top