August 27, 2009 at 12:11 pm
Here is my trigger :-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [insAssemblyParts] ON [dbo].[Schedule_EMPD_SD]
FOR INSERT
AS
declare @drqj varchar(10)
set @drqj =( select drqj from inserted )
declare @wr02 varchar(5)
set @wr02 = (select wr02 from inserted )
declare @sqna varchar(5)
set @sqna = (select sqna from inserted)
declare @wr01 varchar(5)
declare @qnty int
set @qnty=(select qnty from inserted)
set @wr01= (select wr01 from inserted)
declare @litm varchar(35)
set @litm = (select litm from Schedule_Empd where sqna=@sqna and drqj=@drqj and wr02=@wr02 )
declare @uorg int
set @uorg = (select uorg from Schedule_Empd where sqna=@sqna and drqj=@drqj and wr02=@wr02 )
declare @sdate datetime
set @sdate = (SELECT DATEADD(dd, CONVERT(int, RIGHT(@drqj, 3)) - 1, CONVERT(datetime,SUBSTRING(@drqj,1,2)+'0101', 212)))
declare @qty int
if (select wr01 from inserted) ='1000' or (select wr01 from inserted) ='2000' or (select wr01 from inserted) ='2005'
begin
set @qty=@uorg * @qnty
insert into AssemblyParts( sqna,litm,wr01,ScheduleDate,QtyNeeded) values(@sqna,@litm,@wr01,@sdate,@qty)
end
if (select wr01 from inserted) ='3302'
if (select count(*) from AssemblyParts where sqna=@sqna and wr01=@wr01 and scheduledate=@sdate and litm=@litm) > 0
begin
set @qty = 2* @uorg * @qnty
update AssemblyParts set QtyNeeded=@qty where sqna=@sqna and wr01=@wr01 and scheduledate=@sdate and litm=@litm)
end
else
set @qty=@uorg * @qnty
insert into AssemblyParts(sqna,litm,wr01,ScheduleDate,QtyNeeded) values(@sqna,@litm,@wr01,@sdate,@qty)
And it is giving me following error:-
Msg 170, Level 15, State 1, Procedure insAssemblyParts, Line 35
Line 35: Incorrect syntax near ')'.
Can anyone tell me where i am wrong?
August 27, 2009 at 12:38 pm
Is this SQL 2000 or 2008, seeing as you posted in both forums?
Before getting to the syntax error, there's a major design error in your trigger. Triggers are fired once for an insert/update/delete and all the rows affected are placed into the inserted and deleted pseudotables. Your trigger is written assuming there will only be one row in inserted.
set @drqj =( select drqj from inserted )
What happens if there's more than one row in inserted?
Before worrying about the syntax error, I suggest that you rewrite the trigger to work no matter how many rows are in inserted. I don't mean by adding a cursor or a while loop.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 12:44 pm
Its SQl2000.
I am completely newbie so can you give me an example with cursor for above trigger?
August 27, 2009 at 1:05 pm
I said not by adding a cursor or a while loop.
Think of what you need to do to the resultset as a whole, not one row at a time. You should be working with sets of rows, not individual rows.
The first portion of the trigger can be converted as follows:
ALTER TRIGGER [insAssemblyParts] ON [dbo].[Schedule_EMPD_SD]
FOR INSERT
INSERT INTO AssemblyParts( sqna,litm,wr01,ScheduleDate,QtyNeeded)
SELECT i.sqna, SE.litm, i.wr01,
(SELECT DATEADD(dd, CONVERT(int, RIGHT(i.drqj, 3)) - 1, CONVERT(datetime,SUBSTRING(i.drqj,1,2)+'0101', 212))),
SE.uorg*i.qnty
FROM inserted i INNER JOIN Schedule_Empd SE ON i.sqna=SE.sqna and i.drqj=SE.drqj and i.wr02=SE.wr02
WHERE i.wr01 IN ('1000', '2000','2005')
Have a go at the rest yourself, using that as a basis. If you get stuck, post and we'll try to help.
p.s. What is that DATEADD supposed to be doing and what is the drqj colums?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 1:18 pm
thank you sir
August 28, 2009 at 7:06 am
I recommend reading this article[/url] about triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply