April 27, 2013 at 8:11 am
... I played around with it now (sober), but I can't get it to work ... the problem seems to be somewhat to be the Calculation and with that the CBook_ID. The Trigger I got fires ones on Insert on the CourseBookings-Table taking the Information for the Maximum Amount of Aca correctly from the Breakdown Table ...:
CREATE TRIGGER [dbo].[UpdateAca]
ON [dbo].[CourseBookings]
FOR INSERT, UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE CourseBookings
SET [Calc_MAXAca] = cb.[Amount_Aca]
FROM CourseBookings cob
INNER JOIN Breakdown cb
ON cob.[Break_ID] = cb.[Break_ID]
END
GO
I just can't work out why the Amount_Aca is restricted to a Value of 0, 1, or 2 if I enter any new Records in WagesEntryNew for a Break_ID = 3, which allows 5 Aca, whereas I can enter the Total of 5 (which would be the Max. for all Courses) into most of the others ...
LOST - getting back to it Monday i think 🙂
April 29, 2013 at 7:39 am
carsten.crystaldive (4/27/2013)
... I played around with it now (sober), but I can't get it to work ... the problem seems to be somewhat to be the Calculation and with that the CBook_ID. The Trigger I got fires ones on Insert on the CourseBookings-Table taking the Information for the Maximum Amount of Aca correctly from the Breakdown Table ...:
CREATE TRIGGER [dbo].[UpdateAca]
ON [dbo].[CourseBookings]
FOR INSERT, UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE CourseBookings
SET [Calc_MAXAca] = cb.[Amount_Aca]
FROM CourseBookings cob
INNER JOIN Breakdown cb
ON cob.[Break_ID] = cb.[Break_ID]
END
GO
I just can't work out why the Amount_Aca is restricted to a Value of 0, 1, or 2 if I enter any new Records in WagesEntryNew for a Break_ID = 3, which allows 5 Aca, whereas I can enter the Total of 5 (which would be the Max. for all Courses) into most of the others ...
LOST - getting back to it Monday i think 🙂
Unless you have a need to persist this information my opinion is that doing this in triggers is going to be nothing but pain in the long run. You are going to have to create a delete trigger too to put the hours back if a row is deleted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 4, 2013 at 1:35 am
Hey Sean,
a German Final in Wembley ... lol 🙂
Now if you try to update any of your existing data and increase the Amount_ACA or insert a new Wages row it will throw an exception. Give this a spin and see if this is close to what you are looking for.
I played around with it a bit ... I added a Column Calc_MAXAca in the CourseBookings-Table and filled it with my values from the Breakdown-Table. I get it working for INSERT as follows:
if OBJECT_ID('CheckACA_Remaining') is not null
drop function CheckACA_Remaining
go
create function CheckACA_Remaining
(
@CBook_ID int
) returns int as begin
declare @AmountACA_Remaining int
select @AmountACA_Remaining = c.Calc_MAXAca - SUM(w.Amount_Aca)
from CourseBookings c
inner join WagesEntryNew w ON c.CBook_ID = w.CBook_ID
join Breakdown b on b.Break_ID = w.Break_ID
where w.CBook_ID = @CBook_ID
group by c.Calc_MAXAca, w.Break_ID, w.CBook_ID, c.CBook_ID
return @AmountACA_Remaining
end
go
alter table WagesEntryNew WITH NOCHECK --the NOCHECK means that existing data will not be evaluated
ADD CONSTRAINT WagesEntryNew_CheckACA_Remaining CHECK (Amount_ACA <= dbo.CheckACA_Remaining(CBook_ID))
If I now Insert new Records they are limited to the total amount of Aca as defined in Calc_MAXAca ... the trick starts when I try to UPDATE a row ... then I am back to where I was and the Constraint throws an Exception.
Any idea where this might come from ???
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply