مشكله مع Trigger

السلام عليكم

ان امكن سكربت كاملا لو سمحت لمعرفة اين يوجد الخطأ؟

السلام عليكم

عند عمل عملية Insert ينطلق التريجر مرة واحدة بغض النظر عن عدد الصفوف التي تم ادخالها لذا عليك معالجة هذه الحالة .

ولاتنسى

اقتباس

ن امكن سكربت كاملا لو سمحت لمعرفة اين يوجد الخطأ؟

USE [Database]
GO
/****** Object:  Trigger [dbo].[CalculateDiscount]    Script Date: 06/27/2010 10:03:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[CalculateDiscount]
on [dbo].[Attendance]
after insert,update
as 



Delete from discount 
where discount.date in(select top 1 date from inserted order by ProcID Desc) and 
                           discount.empID in (select top 1 fk_EmpID from inserted order by ProcID Desc)


--Attendace Minutes
Declare @Minutes Nvarchar(255)
Select top 1 @Minutes=substring(Convert(nvarchar(255),attendTime),4,2) From Attendance order by ProcID Desc

--Attendace Hours
Declare @Hours Nvarchar(255)
Select top 1 @Hours=substring(Convert(nvarchar(255),attendTime),1,2) From Attendance order by ProcID Desc

--start time
Declare @StartTime nvarchar(255)
Select @StartTime=start from WorkingData

--attend date
Declare @Date Date
Select top 1 @Date=Date From Attendance order by ProcID Desc


--salary of current employee
Declare @Salary money
Select @salary=salary 
from Employees

where Employees.empID in (select top 1 fk_EmpID from Attendance order by ProcID Desc)


Declare @SanctionDays int
Declare @SanctionPer Decimal(18,2)


--calculating


       --======================NSERT=========================

       IF (@Hours is NULL)
       Begin






							 --IF there is percent value
							IF (Exists(select Percentage from Sanctions where sanction='غياب' and Percentage IS Not Null))
								Begin
								select @SanctionPer=Percentage from Sanctions where Sanction='غياب'
								Declare @PerDayC money
								Declare @xC money
								set @xC=@salary/30
								Set @PerDayC=(@SanctionPer*(@salary/30))

								End
							--IF there is DaysValues	
							IF (Exists(select Daysnumber from Sanctions where sanction='غياب' and Daysnumber IS Not Null ))
								Begin
								select @SanctionDays=Daysnumber from Sanctions where Sanction='غياب'
								Declare @PerDayD money
								Set @PerDayD=@SanctionDays*(@salary/30)

								End

								--Inserting Values
								IF @PerDayC is not null and @PerDayD is not null
								Begin
								insert into  discount values ( @PerDayC+@PerDayD ,(select inserted.date from inserted),(select fk_EmpID from inserted),(select ProcID from Sanctions where sanction='غياب'))
					            End
					            else
					            IF @PerDayC is not null
					            Begin
					            insert into  discount values ( @PerDayC ,(select inserted.date from inserted),(select fk_EmpID from inserted),(select ProcID from Sanctions where sanction='غياب'))
					            ENd
					            else
					            IF @PerDayD is not null
					            Begin
					            insert into  discount values (@PerDayD ,(select inserted.date from inserted),(select fk_EmpID from inserted),(select ProcID from Sanctions where sanction='غياب'))
					            End








RETURN

END




-----------------------------


Declare @y int
set @y=1
--Working in Minutes--
Declare @MinuteAfterCalc int

Select @MinuteAfterCalc=DateDiff(Minute,(select start from workingData),(select top 1 attendTime from inserted))
IF (@Minutes<>substring(convert(nvarchar(255),@startTime),4,2))
Begin
        WHILE  (@y<=1440)

        BEGIN 

                IF @y=1 or @y=2 or @y=3 or @y=4 or @y=5 or @y=6 or @y=7 or @y=8 or @y=9
                 BEGIN 

					   IF (@MinuteAfterCalc='0'+Convert(varchar(255),@Y))
						BEGIN



							 --IF there is percent value
							 IF (Exists(select Percentage from Sanctions where sanction='0'+Convert(varchar(255),@Y) and Percentage IS Not Null))
								Begin

								select @SanctionPer=Percentage from Sanctions where Sanction='0'+Convert(varchar(255),@Y)
								Declare @PerDayE money
								Declare @xE money
								set @xE=@salary/30
								Set @PerDayE=(@SanctionPer*(@salary/30))

								End

							--IF there is DaysValues	
							IF (Exists(select Daysnumber from Sanctions where sanction='0'+Convert(varchar(255),@Y) and Daysnumber IS Not Null ))
								Begin
								select @SanctionDays=Daysnumber from Sanctions where Sanction='0'+Convert(varchar(255),@Y)
								Declare @PerDayF money
								Set @PerDayF=@SanctionDays*(@salary/30)

								End

							--Inserting ValuesIF @PerDayC is not null and @PerDayD is not null
								IF @PerDayE is not null and @PerDayF is not null
								Begin
								INSERT INTO discount Values(@PerDayE+@PerDayF,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction='0'+Convert(varchar(255),@Y)))  	   
					            End
					            else
					            IF @PerDayE is not null
					            Begin
					            INSERT INTO discount Values(@PerDayE,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction='0'+Convert(varchar(255),@Y)))  	   
					            ENd
					            else
					            IF @PerDayF is not null
					            Begin
					            INSERT INTO discount Values(@PerDayF,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction='0'+Convert(varchar(255),@Y)))  	   
					            End	

			            END



         END



           ELSE

           BEGIN
           IF (@MinuteAfterCalc=@y)
						BEGIN


							 --IF there is percent value
							 IF (Exists(select Percentage from Sanctions where sanction=Convert(varchar(255),@Y) and Percentage IS Not Null))
								Begin
								select @SanctionPer=Percentage from Sanctions where Sanction=Convert(varchar(255),@Y)
								Declare @PerDayG money
								Declare @xG money
								set @xG=@salary/30
								Set @PerDayG=(@SanctionPer*(@salary/30))

								End
							--IF there is DaysValues	
							IF (Exists(select Daysnumber from Sanctions where sanction=Convert(varchar(255),@Y) and Daysnumber IS Not Null ))
								Begin
								select @SanctionDays=Daysnumber from Sanctions where Sanction=Convert(varchar(255),@Y)
								Declare @PerDayH money
								Set @PerDayH=@SanctionDays*(@salary/30)

								End

							--Inserting Values
							IF @PerDayG is not null and @PerDayH is not null
								Begin
								INSERT INTO discount Values(@PerDayG+@PerDayH,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction=Convert(varchar(255),@Y)))  	   
					            End
					            else
					            IF @PerDayH is not null
					            Begin
					            INSERT INTO discount Values(@PerDayH,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction=Convert(varchar(255),@Y)))  	   
					            ENd
					            else
					            IF @PerDayG is not null
					            Begin
					            INSERT INTO discount Values(@PerDayG,@Date,(select inserted.fk_EmpID from inserted),(select ProcID from Sanctions where sanction=Convert(varchar(255),@Y)))  	   
					            End	


			            END

           END


            set @y=@y+1      

END





END





-- END