Here's what you do to turn off Nested Triggers in SQL Server 2000.
USE master
EXEC sp_configure 'nested triggers', '0'
RECONFIGURE WITH OVERRIDE
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 2:42:48 PM >>>
I think I figured out this error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I update the field. The trigger is called. The trigger updates the field. The trigger is called. The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.
Repeat as necessary ;)
So, I guess my question is what can I put in the trigger to tell it to stop after it updates and not to call itself?
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 2:22:07 PM >>>
Thanks Steve :)
That was one of the problems. The other was that there was a trigger on the column. I had to remove it before running the update.
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 2:07:35 PM >>>
Doh.
Try this:
update oncall_shifts set shift_start = cast((cast(start_month as varchar) +
'/' + cast(start_day as varchar) + '/' + cast(start_year as varchar)) as
datetime)
sorry about that.
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 1:53 PM
To: CF-Talk
Subject: RE: Dates - ranges overlapping months... rut-roh
Hey Steve!
I tried that one as well. I kept getting:
Syntax error converting the varchar value '/' to a column of data type int.
So then I went to:
update oncall_shifts set shift_start = cast((start_month + start_day +
start_year) as datetime)
and got:
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).
Thoughts? *meanwhile, I'll keep trying*
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org <http://www.childrensdayton.org>
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 1:33:06 PM >>>
Candace,
This should be :
update oncall_shifts set shift_start = cast((start_month + '/' + start_day +
'/' + start_year) as datetime)
Steve
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 1:28 PM
To: CF-Talk
Subject: Re: Dates - ranges overlapping months... rut-roh
Hey Jochem, I am using Transact-SQL and converted everything but:
UPDATE TABLE table SET shift_start = to_date( start_year || '-'
|| start_month || '-' || start_day, "yyyy-mm-dd");
I tried to do:
UPDATE ONCALL_SHIFTS
SET shift_start = CAST(start_month&start_day&start_year AS DATETIME)
But it is setting the shift_start to 1/1/1900??
Thanks for all your help!!
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org <http://www.childrensdayton.org>
<http://www.childrensdayton.org>
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 11:22:01 AM >>>
Candace Cottrell wrote:
>
> <cfqueryparam cfsqltype="cf_sql_integer" value="#page[j][i]#"> BETWEEN
START_DAY AND END_DAY
> AND
> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.M#"> BETWEEN
START_MONTH AND END_MONTH
> AND
> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.Y#"> BETWEEN
START_YEAR AND END_YEAR
>
> does not work when you have a date range that spans more than one month.
> Start_Month = 1
> Start_Day = 18
> Start Year = 2004
>
> End_Month = 2
> End_Day = 8
> End_Year = 2004
>
> Basically, while looping, if my #page[j][i]# was on the number 3, it would
return false, because 3 is not between 18 and 8.
How about creating an extra field that stores a date and query
that? You do not have to change any UI for that, you just
maintain it through a trigger.
Pseudocode (PL/SQL has been a while):
ALTER TABLE table ADD COLUMN shift_start DATE;
UPDATE TABLE table SET shift_start = to_date( start_year || '-'
|| start_month || '-' || start_day, "yyyy-mm-dd");
CREATE TRIGGER ON table AFTER UPDATE, INSERT
BEGIN
SET NEW:shift_start = to_date( start_year || '-' ||
start_month || '-' || start_day, "yyyy-mm-dd");
END;
After this, your shift_start will always contain the start date.
Do the same for the end_date and you can do a simple between on
the entire date.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
