incorrect syntax help with trigger

I have the following code and have the following incorrect syntax at the FROM and END.  How do I eliminate these errors

CREATE TRIGGER [dbo].[WarrantyCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
   UPDATE ServiceRequestAddFru   
SET OrderTypeID = 4
FROM ServiceRequestAddFru
JOIN    (select Count(a.SerialNo)
                  FROM ServiceRequestAddFru a
				inner join Frus b
    on a.FrusID = b.FrusID 
	 where a.CustomerID=inserted.CustomerID and a.SerialNo=inserted.SerialNo 
				 and b.Repairable=1 and 
				a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)
				group by SerialNo
				having Count(a.SerialNo) >1 
   FROM ServiceRequestAddFru JOIN inserted
   ON ServiceRequestAddFru.CustomerID= inserted.CustomerID and ServiceRequestAddFru.SerialNo=inserted.SerialNo
      and ServiceRequestAddFru.SRequestID=inserted.SRequestID 
END;

Open in new window

JDay2Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
So for  one column you have another criteria and one for other?

Please try this then-

CREATE TRIGGER [dbo].[TimesInCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
   UPDATE ServiceRequestAddFru  
   SET TimesIn = (select Count(a.SerialNo) 
                  FROM ServiceRequestAddFru a
				inner join Frus b
    on a.FrusID = b.FrusID 
    where a.CustomerID=inserted.CustomerID and a.SerialNo=inserted.SerialNo 
				 and b.Repairable=1 and 
				a.ReceiptDate >= DATEADD(yy, -1, a.ReceiptDate))  
   FROM ServiceRequestAddFru JOIN inserted
   ON ServiceRequestAddFru.CustomerID= inserted.CustomerID and ServiceRequestAddFru.SerialNo=inserted.SerialNo
      and ServiceRequestAddFru.SRequestID=inserted.SRequestID 

	
   UPDATE ServiceRequestAddFru   
	SET OrderTypeID = 4
	FROM 
		ServiceRequestAddFru
		INNER JOIN inserted ON ServiceRequestAddFru.CustomerID= inserted.CustomerID
								and ServiceRequestAddFru.SerialNo=inserted.SerialNo
								and ServiceRequestAddFru.SRequestID=inserted.SRequestID 
		where exists (
						select  NULL
						  FROM ServiceRequestAddFru a
							inner join Frus b on a.FrusID = b.FrusID 
							INNER JOIN inserted i ON a.CustomerID=i.CustomerID and a.SerialNo=i.SerialNo 
						where b.Repairable=1 and a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)
						group by SerialNo
						having Count(*) >1
					)
END

Open in new window

0
 
JDay2Author Commented:
errors on this line
FROM ServiceRequestAddFru JOIN inserted
0
 
Bill PrewCommented:
There is a lot going on in that query, you probably need to provide an explanation of what you are trying to do and how the tables relate a bit.  It's hard to know where some of the JOIN versus WHERE criteria might belong without know how you are trying to get the query to work.


»bp
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
JDay2Author Commented:
What I am trying to accomplish?

I want to set ordertypeid to 4 which is warranty in table servicerequestaddfru only if the serialno in that table has been in the last 3 months(hence counting the serial>1) based on receipt date otherwise I want the ordertypeid to stay the same.

I have joined the frusid table because I only want parts that are repairable.

What I am struggling with is joining the inserted table.  I need to join this because my program will update in batch sometimes and I need it to update multiple rows.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You forgot to close one parenthesis:
having Count(a.SerialNo) >1 )
0
 
JDay2Author Commented:
by adding the parenthesis

my errors now are

 where a.CustomerID=inserted.CustomerID and a.SerialNo=inserted.SerialNo
the multi-part identifier could not be bound on the highlighted

FROM ServiceRequestAddFru
Incorrect Syntax on From and invalid column name on ServiceRequestAddFru
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, you didn't add the inserted table in your SELECT.
Here's your query fixed:
CREATE TRIGGER [dbo].[WarrantyCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
	UPDATE ServiceRequestAddFru   
	SET OrderTypeID = 4
	FROM ServiceRequestAddFru
		JOIN (select Count(a.SerialNo)
              FROM ServiceRequestAddFru a
				inner join Frus b on a.FrusID = b.FrusID 
				INNER JOIN inserted i ON a.CustomerID=i.CustomerID and a.SerialNo=i.SerialNo 
			where b.Repairable=1 and a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)
			group by SerialNo
			having Count(a.SerialNo) >1 )
	JOIN inserted ON ServiceRequestAddFru.CustomerID= inserted.CustomerID
		and ServiceRequestAddFru.SerialNo=inserted.SerialNo
		and ServiceRequestAddFru.SRequestID=inserted.SRequestID 
END;

Open in new window

0
 
JDay2Author Commented:
I tweaked it and the only error is at the END;=incorrect syntax near the end

CREATE TRIGGER [dbo].[WarrantyCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
	UPDATE ServiceRequestAddFru   
	SET OrderTypeID = 4
	FROM ServiceRequestAddFru
		JOIN (select Count(a.SerialNo)as tt
              FROM ServiceRequestAddFru a
				inner join Frus b on a.FrusID = b.FrusID 
				INNER JOIN inserted i ON a.CustomerID=i.CustomerID and a.SerialNo=i.SerialNo 
			where b.Repairable=1 and a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)
			group by a.SerialNo
			having Count(a.SerialNo) >1 ) as tt
	JOIN inserted ON CustomerID= inserted.CustomerID
		and SerialNo=inserted.SerialNo
		and SRequestID=inserted.SRequestID 
END;

Open in new window

0
 
JDay2Author Commented:
Victor,

The code you gave me it errors on
JOIN inserted ON ServiceRequestAddFru.CustomerID= inserted.CustomerID
            and ServiceRequestAddFru.SerialNo=inserted.SerialNo
            and ServiceRequestAddFru.SRequestID=inserted.SRequestID
END;
JOIN error is incorrect syntax near 'join'. expecting  AS,ID, or QUOTED_ID
The ServiceRequestaddfru error The multi part identifier could not be bound
Incorrect syntax near the end
0
 
Pawan KumarDatabase ExpertCommented:
Why you are counting - Count(a.SerialNo) ? Where you will need it?
0
 
Pawan KumarDatabase ExpertCommented:
Please try this -Count(a.SerialNo)  is not required as we not using it any where.

CREATE TRIGGER [dbo].[WarrantyCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
	UPDATE ServiceRequestAddFru   
	SET OrderTypeID = 4
	FROM 
		ServiceRequestAddFru
		INNER JOIN inserted ON ServiceRequestAddFru.CustomerID= inserted.CustomerID
								and ServiceRequestAddFru.SerialNo=inserted.SerialNo
								and ServiceRequestAddFru.SRequestID=inserted.SRequestID 
		where exists (
						select  NULL
						  FROM ServiceRequestAddFru a
							inner join Frus b on a.FrusID = b.FrusID 
							INNER JOIN inserted i ON a.CustomerID=i.CustomerID and a.SerialNo=i.SerialNo 
						where b.Repairable=1 and a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)
						group by SerialNo
						having Count(*) >1
					)
END

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try this. Note triggers works row by row. So Trigger will update only 1 row at a time.

CREATE TRIGGER [dbo].[WarrantyCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
	UPDATE ServiceRequestAddFru   
	SET OrderTypeID = 4
	FROM 
		ServiceRequestAddFru a
		INNER JOIN inserted ON a.CustomerID= inserted.CustomerID
								and a.SerialNo=inserted.SerialNo
								and a.SRequestID=inserted.SRequestID 
		INNER JOIN Frus b on a.FrusID = b.FrusID 		
		WHERE b.Repairable = 1 and a.ReceiptDate >= DATEADD(month, -3, a.ReceiptDate)		
END

Open in new window

0
 
Scott PletcherSenior DBACommented:
Note triggers works row by row. So Trigger will update only 1 row at a time.

That's 100% false.  Triggers in SQL Server work on sets of rows only.  Even if you wanted to, it's impossible in SQL Server to create a trigger that will fire separately for every row inserted or updated.  That is, say 5 rows are inserted or updated in one statement, the trigger will still fire only once, period.
0
 
JDay2Author Commented:
I need the count of Serial number because if it has been in last three months that is what should change the ordertype to warranty.

I tried your code and I am getting a maximum stored procedure,function,trigger, or view nesting level exceeded(limit32).
0
 
JDay2Author Commented:
Is this error because I have another trigger on that table?

CREATE TRIGGER [dbo].[TimesInCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
   UPDATE ServiceRequestAddFru  
   SET TimesIn = (select Count(a.SerialNo) 
                  FROM ServiceRequestAddFru a
				inner join Frus b
    on a.FrusID = b.FrusID 
    where a.CustomerID=inserted.CustomerID and a.SerialNo=inserted.SerialNo 
				 and b.Repairable=1 and 
				a.ReceiptDate >= DATEADD(yy, -1, a.ReceiptDate))  
   FROM ServiceRequestAddFru JOIN inserted
   ON ServiceRequestAddFru.CustomerID= inserted.CustomerID and ServiceRequestAddFru.SerialNo=inserted.SerialNo
      and ServiceRequestAddFru.SRequestID=inserted.SRequestID 

Open in new window


How do I combine
0
 
Pawan KumarDatabase ExpertCommented:
Please see this works, use only single trigger. If need modifications pls inform.
 
CREATE TRIGGER [dbo].[TimesInCalc]  
ON [dbo].[ServiceRequestAddFru]  
FOR INSERT, UPDATE AS  
BEGIN
   UPDATE ServiceRequestAddFru  
   SET OrderTypeID = 4,
   TimesIn = (select Count(a.SerialNo) 
                  FROM ServiceRequestAddFru a
				inner join Frus b
    on a.FrusID = b.FrusID 
    where a.CustomerID=inserted.CustomerID and a.SerialNo=inserted.SerialNo 
				 and b.Repairable=1 and 
				a.ReceiptDate >= DATEADD(yy, -1, a.ReceiptDate))  
   FROM ServiceRequestAddFru JOIN inserted
   ON ServiceRequestAddFru.CustomerID= inserted.CustomerID and ServiceRequestAddFru.SerialNo=inserted.SerialNo
      and ServiceRequestAddFru.SRequestID=inserted.SRequestID 

Open in new window

0
 
JDay2Author Commented:
That trigger is missing the criteria for the set the ordertypeid from the second trigger.  It should set only if the serialno has been in the last three months.

0
 
JDay2Author Commented:
Sorry for the delay.  Project got put on hold for other priorities.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.