Trigger Help Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Help Please

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-23 : 10:52:58

I have the following trigger, which seems logical to me, but I'm getting an error when I meet the action which fires the trigger.

Basically I know it will be due to the fact that in tbl_holidays, the HolidayDate field is stored as a char(10) instead of a datetime. This can't be changed now though.

However, I dont know how to fix it.
Can anyone please help me find what I need to change to fix it ?

Thanks in advance.

==============
CREATE TRIGGER [tr_HolidayGroupChanged] ON [dbo].[tbl_EmployeeDetails]
FOR INSERT, UPDATE

AS

-- Declare Variables For Use In Trigger
DECLARE @ChangeEmployeeNumber varchar(10)
DECLARE @HolidayGroup varchar(50)

-- Declare Variables Being Read By Cursor
DECLARE @EmployeeNo varchar(10)
DECLARE @HolidayDate datetime
DECLARE @AM bit
DECLARE @PM bit
DECLARE @NumberofDays float
DECLARE @NumberofHours float
DECLARE @Extra_Reason varchar(1000)

-- If HolidayGroup Is Changed
IF UPDATE(HolidayGroup)

-- Set EmployeeNumber To Be The Changed Employee
SELECT @ChangeEmployeeNumber = EmployeeNumber, @HolidayGroup = HolidayGroup FROM Deleted

-- Declare Cursor And Fields To Bring From Table
DECLARE HolidayGroupChangeCursor CURSOR READ_ONLY
FOR
SELECT TOP 100 PERCENT EmployeeNumber, CAST(HolidayDate as datetime), AM, PM, NumberOfDays, NumberOfHours, Extra_Reason
FROM dbo.tbl_Holidays
WHERE (EmployeeNumber = @ChangeEmployeeNumber) AND (HolidayDate >= GETDATE())
ORDER BY CAST(HolidayDate as datetime)

-- Open Cursor For Use
OPEN HolidayGroupChangeCursor

-- Perform FETCH To Bring Data From Table And Store In Variables
FETCH NEXT FROM HolidayGroupChangeCursor
INTO @EmployeeNo, @HolidayDate, @AM, @PM, @NumberofDays, @NumberofHours, @Extra_Reason

-- Do While There Are No More Rows To Fetch
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
-- Call Stored Procedure And Pass In Holiday Group and Date
EXEC sp_FillFreeHolidays_Limitchanged @HolidayDate, @HolidayGroup
END

-- Perform FETCH To Bring Data From Table And Store In Variables As Long As Previous FETCH Succeeds
FETCH NEXT FROM BookedHolidaysCursor
INTO @EmployeeNo, @HolidayDate, @AM, @PM, @NumberofDays, @NumberofHours, @Extra_Reason

END

-- Close and Release From Memory
CLOSE BookedHolidaysCursor
DEALLOCATE BookedHolidaysCursor
===============

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-23 : 11:09:55

One problem with your trigger is that it assumes only one row will ever be updated at a time:
(based on your assignment of @ChangeEmployeeNumber from deleted)
If deleted has 10 different employees you will only act on one of them.

Another problem is that your cursor pulls from deleted but your trigger is for insert,delete. On inserts the deleted table will be empty. Perhaps you omitted code specific to inserts just for this post?

Now regarding your question, what is the error you get? Is it that some holidayDates (char(10)) won't convert to datetime?

Be One with the Optimizer
TG

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-23 : 11:16:57

Hi TG,

Thank you for taking a look at this for me.
Basically only 1 row will be updated at a time so this will be fine.
Will I need to change anything for this then ?

As for the delete part, no I didn't omit anything - I'm just really new to triggers and SQL so I've obviously got this wrong.
The reason I went for the deleted part was I need the person's old holiday group, not their new one.

Finally, the error I'm getting when I change the group in the employee details table is :

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-23 : 13:14:34

I guess the next question is do any of the char(10) values convert successfully to datetime?
Assuming most of the values are "ok" what do you want to have happen for the "bad" ones. Just ignore those rows? Or perhaps you want go in and correct any bogus values.

On the other hand if none of these char(10) values convert succesffuly to datetime than post some sample values so we can see how these "dates" are represented.

Be One with the Optimizer
TG

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 03:01:38

Hi TG,

I can't tell if any of the Char(10) values convert, because as soon as I try to change the holiday group, I get this error.

If I use query analyser on the select part of my trigger, the date appears as "2008-06-24 00:00:00.000"

I've tried taking the cast part off, and the date appears as "24/06/2008" And whilst I don't get as many errors, I still get the following screen :

I then wondered if it was to do with the stored procedure expecting a date, so I added the following lines above the call to the sp :

DECLARE @ConvertedDate as datetime
SELECT @ConvertedDate = CAST(@HolidayDate as datetime)

But I get the exact same error message.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 03:28:17

Also the logic about IF UPDATE(HolidayGroup) is flawed.

If another column is updated the code still moves on but with @ChangeEmployeeNumber and @HolidayGroup with NULL values.

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 03:30:31

Thanks Peso,

How can I fix this, as I really only want the code to be activated when the holiday group is updated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 03:37:26

[code]CREATE TRIGGER tr_HolidayGroupChanged ON dbo.tbl_EmployeeDetails
FOR INSERT,
UPDATE
AS

SET NOCOUNT ON

-- Declare Variables Being Read By Cursor
DECLARE @EmployeeNo VARCHAR(10),
@HolidayDate DATETIME,
@AM BIT,
@PM BIT,
@NumberofDays FLOAT,
@NumberofHours FLOAT,
@Extra_Reason VARCHAR(1000)

DECLARE @Yak TABLE
(
EmployeeNumber VARCHAR(10),
HolidayGroup VARCHAR(50)
)

IF EXISTS (SELECT * FROM deleted) -- UPDATE
INSERT @Yak
(
EmployeeNumber,
HolidayGroup
)
SELECT DISTINCT i.EmployeeNumber,
i.HolidayGroup
FROM inserted AS i
INNER JOIN deleted AS d ON d.EmployeeNumber = i.EmployeeNumber
WHERE i.HolidayGroup <> d.HolidayGroup
ELSE -- INSERT
INSERT @Yak
(
EmployeeNumber,
HolidayGroup
)
SELECT DISTINCT EmployeeNumber,
HolidayGroup
FROM inserted

-- Declare Cursor And Fields To Bring From Table
DECLARE HolidayGroupChangeCursor CURSOR READ_ONLY
FOR
SELECT TOP 100 PERCENT
h.EmployeeNumber,
CAST(h.HolidayDate as DATETIME),
h.AM,
h.PM,
h.NumberOfDays,
h.NumberOfHours,
h.Extra_Reason
FROM dbo.tbl_Holidays AS h
INNER JOIN @Yak AS y ON y.EmployeeNumber = h.EmployeeNumber
WHERE HolidayDate >= GETDATE()
ORDER BY CAST(h.HolidayDate AS DATETIME)

-- Open Cursor For Use
OPEN HolidayGroupChangeCursor

-- Perform FETCH To Bring Data From Table And Store In Variables
FETCH NEXT FROM HolidayGroupChangeCursor
INTO @EmployeeNo, @HolidayDate, @AM, @PM, @NumberofDays, @NumberofHours, @Extra_Reason

-- Do While There Are No More Rows To Fetch
WHILE @@FETCH_STATUS = 0
BEGIN
-- Call Stored Procedure And Pass In Holiday Group and Date
EXEC sp_FillFreeHolidays_Limitchanged @HolidayDate, @HolidayGroup

-- Perform FETCH To Bring Data From Table And Store In Variables As Long As Previous FETCH Succeeds
FETCH NEXT FROM BookedHolidaysCursor
INTO @EmployeeNo, @HolidayDate, @AM, @PM, @NumberofDays, @NumberofHours, @Extra_Reason
END

-- Close and Release From Memory
CLOSE BookedHolidaysCursor
DEALLOCATE BookedHolidaysCursor[/code]

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 03:50:18

Thanks for helping me with this Peso, I really appreciate it.

Your code is giving me an error as per below :

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 04:08:34

One of these columns need to be collated on-the-fly

INNER JOIN	@Yak AS y ON y.EmployeeNumber = h.EmployeeNumber

Either

INNER JOIN	@Yak AS y ON y.EmployeeNumber COLLATE { ... } = h.EmployeeNumber

INNER JOIN	@Yak AS y ON y.EmployeeNumber = h.EmployeeNumber COLLATE { ... }

I can't tell which to use nor which collation name to use since I don't know your system.
But! This is an excellent opportunity for you to read Books Online and learn more!

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 04:20:29

Ignore

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 04:35:27

Hi Peso,

I've just been reading BOL about this message as it happens and found the COLLATE part, but I wasn't sure where to put it.
Thanks again.

BTW, I've used your code, and I can see it is much better than mine and will account for the scenario I want.
It now works fine and I'm getting no errors, but for some reason it's not doing what it should to the rows based on the stored procedure.

From my limited understanding, It seems there is no way for me to debug through the trigger. Is this correct ?
If I could step through the trigger I think I could resolve my problem fairly easily now you've given my this help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 04:37:24

Can it be that EXEC sp_FillFreeHolidays_Limitchanged @HolidayDate, @HolidayGroup
does not accept EmployeeNumber? How does the SP know for which person the Holidaydate is to fill?

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 04:48:37

No because the sp doesn't care which employee to update.

Basically to give you a bit of background on what the sp does (Sorry I should have done this earlier).

This sp was originally called when a holiday limit was changed in the limits table.
So for example, you may have a limit of 2 people on 10th May.
Therefore there would be 2 holiday entries sitting on the main holiday list (tbl_Holidays)

If the limit is then changed to 4, the trigger on the limit table calls this stored procedure.

The sp then checks the reserve holidays list (tbl_HolidayRequests) and if there are holidays waiting in reserve for that day and holiday group, the sp moves them onto the main list until the limit is reached. So in this case 2 holidays would be moved from the reserve list to the main list.

Now obviously this all works fine, but there is no trigger for when a holiday group is changed hence what I want to do.

So basically once a holiday group is changed, in effect it means the person has moved out of the original holiday group and therefore created a space. So I want the sp to run for that date and the holiday group, and move a holiday into its place from the reserve list.

Does this make sense ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 05:11:15

Yes.
But it relies on that the "old" value for holidaygroup is removed, right?
With the trigger above you seems to only casre for the new value. What will happen to the old value?

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 05:12:38

Edited to word better.

Yes It's the old value I need, as this is the group that now contains the space for someone else to mvoe into.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 05:17:39

Only for updated rows. Change

SELECT DISTINCT	i.EmployeeNumber,
		i.HolidayGroup

to this

SELECT DISTINCT	i.EmployeeNumber,
		d.HolidayGroup

to get old value for HolidayGroup.

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 05:20:47

Thanks Peso.

Ok, I've done this and I'm still in the same position as before where it doesn't give me errors, but it doesn't move the holidays either.

Is there any way I can see what values are being passed into the stored procedure ?
If I could do this, I could debug through the sp.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 05:26:40

Of course.
Create a table MyTriggerValues with

CREATE TABLE MyTriggerValues (rowid int identity(1, 1), dt DATETIME, hGroup VARCHAR(50))

and then in the trigger, just before exec the SP, do an insert

INSERT MyTriggerValues (dt, hgroup) values (@HolidayDate, @HolidayGroup)

and then you can check all values passed to the SP with

SELECT * FROM MyTriggerValues ORDER BY rowid

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 06:01:07

Thanks Peso, that's excellent.

The good news is that I can now see exactly what is being passed into the sp.
The bad news is that the values being passed are correct (i.e. '18/06/2008' and 'TestingABC')

However, I can look into this.

Thank you so much for all of your help, I can't thank you enough for your time and patience.
I really appreciate it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 06:01:37

You are welcome.

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources