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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDay2Author Commented:
Sorry for the delay.  Project got put on hold for other priorities.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.