473,700 Members | 2,311 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger Error

I have a table:

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

CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY _ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO

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

and i try to create this trigger, but i fail:

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

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
IF PARENT_CATEGORY _ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY _ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER

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

And i get the following Error using SQL Server 2000:

Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7
Invalid column name 'PARENT_CATEGOR Y_ID'.

Any clue?

Coosa

Jul 23 '05 #1
33 4768
coosa wrote:
[...]

I got the same error as you. However, if you slightly amend the
trigger thus:

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT, @C AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
SELECT @C = PARENT_CATEGORY _ID FROM INSERTED
IF @C IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY _ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER

This works. Don't know why, but when Erland comes by, I expect he'll
tell us!

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

Jul 23 '05 #2
On 25 May 2005 00:48:46 -0700, coosa wrote:
I have a table:

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

CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY _ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO

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

and i try to create this trigger, but i fail:

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

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
IF PARENT_CATEGORY _ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY _ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER

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

And i get the following Error using SQL Server 2000:

Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7
Invalid column name 'PARENT_CATEGOR Y_ID'.

Any clue?

Coosa
Hi Coosa,

There are several problems in your code.
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
This seems to indicate that you expect the trigger to process only one
row at a time. In SQL Server, a trigger is executed once after a
statement is finished - so if an INSERT statement 4 inserts rows at
once, the trigger is fired once, and has to process all 4 new rows
(which can be extracted from the inserted pseudo-table).
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
First, there is no table AI_CATEGORY in your post.
Second, you use an update statement, but you only change the values of
some variables. This statement will process all rows from AI_CATEGORY
and set both variables for each row. In the end, you'll have assigned
the value 1 to @COUNTER numerous times, and @TEMP_ID will be the
category_id of whatever row happens to be processed last. This can
change from execution to execution, and will have no relation to the row
(or collection of rows) that was/were inserted in the statement that
caused this trigger to fire.
IF PARENT_CATEGORY _ID IS NOT NULL
This is illegal syntax. Replace PARENT_CATEGORY _ID with either a local
variable or a query that returns exactly one value in order to pass the
syntax check.
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY _ID,
@COUNTER = @COUNTER + 1
Like the previous statement, this will process all rows from
AI_CATEGORY. The value of @COUNTER wil increase rapidly (it will be
incremented by 1 for each row in AI_CATEGORY). And @TEMP_ID will again
be the PARENT_CATEGORY _ID of one "randomly chosen" row. If you're lucky,
it'll be NULL and the loop will finish. But there's a good chance that
this will not be NULL - and you might end up waiting while the trigger
is stuck in a loop, until @COUNTER overflows the maximum integer value.
UPDATE CATEGORY
SET DEPTH = @COUNTER


This will set the DEPTH column to the same value in all rows in the
CATEGORY table. I doubt if that's what you want.

I could help you to improve your code, but I honestly don't kknow what
exactly you are trying to achieve. Please post the CREATE TABLE
statement for your other table (AI_CATEGORY) as well, post some sample
data (as INSERT statements) for both tables, then show us an example
INSERT operation and the expected result from the trigger execution. A
brief description of the actual business problem might help as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
Ah thanks,

I modified to the following:

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

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT,
@COUNTER AS INT,
@P_ID AS INT

SELECT @TEMP_ID = CATEGORY_ID,
@COUNTER = 1,
@P_ID = PARENT_CATEGORY _ID
FROM INSERTED
IF @P_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER

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

Now at least when i check the syntax, SQL Server accepts it and i can
then save it under the triggers.
When inserting some records, as long the parent categories are nulls,
it updates the depthes to 1, else it hangs, if the parent category has
some value.
Hmm, maybe u c an help me with an idea, i need to keep track of the
depth of hierarchy.
It means as an example:

CATEGORY_ID CATEGORY_NAME PARENT_CATEGORY _ID DEPTH
-----------------------------------------------------
29 PC <NULL> 1
30 MOBILES <NULL> 1
32 DIGITAL CAMERAS <NULL> 1
33 NETWORKS 29 2
35 AUDIO 29 2
36 VIDEO 29 2
37 AUDIO CARDS 35 3
38 IDE AUDIO CARDS 37 4
-----------------------------------------------------

So when I insert records, i leave the depth empty to accept null
values, but after i insert some records, i want the trigger to be fired
to automatically detect the depth of each category and update this
depth.
Any idea? I don't know what's wrong in the code, but it's definetely a
semantic error since it hangs in run time.

Coosa

Jul 23 '05 #4
Thanks for your reply, please refer to the reply i wrote to Edward.
I'm new in databases and take me as a newbie; what i want is to keep
track of the depth of each node.
In this case i illustrated an example when i replied to Edward. Google
some how displayed the table not as expected, but u could see that i
want to keep track of the depth of each category relating to its
parents. As a root category, the depth will be 1 and this depth will
increment for any next subcategory.
Finally, there is no AI_Category table, it's the name of the trigger!
when i wrote set [...] in the trigger changing the local variable, i
received errors, but when i changed it to update the trigger itself, it
accepted, so i thought it's a way sql server works! :-)

Jul 23 '05 #5
To illustrate a pseudo code, i attempt the following:
1.) Iterate through all rows, row by row
2.) create a temporary variable to equal the current id
3.) Begin a loop in each row
4.) create a counter and initialize to equal 1
5.) For each row, set the temporary variable to equal the parent id (as
linked lists)
6.) increment the counter
7.) End the loop if the temporary variable is null (means it reached
the root category that has no further parents)
8.) Update the depth of that particular row to equal the counter
9.) continue to iterate through the rest of all remaining rows

How can i do it? :-)

Thanks in advance

Jul 23 '05 #6
coosa (co*****@gmail. com) writes:
To illustrate a pseudo code, i attempt the following:
1.) Iterate through all rows, row by row
2.) create a temporary variable to equal the current id
3.) Begin a loop in each row
4.) create a counter and initialize to equal 1
5.) For each row, set the temporary variable to equal the parent id (as
linked lists)
6.) increment the counter
7.) End the loop if the temporary variable is null (means it reached
the root category that has no further parents)
8.) Update the depth of that particular row to equal the counter
9.) continue to iterate through the rest of all remaining rows

How can i do it? :-)


The standard recomendation is that you post:

o CREATE TABLE statement for your table(s), possibly simplified.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
Beside, the trigger code is modfied now, still i run into hanging in
run time due too the loop.

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT,
@COUNTER AS INT,
@P_ID AS INT

SELECT @TEMP_ID = CATEGORY_ID,
@P_ID = PARENT_CATEGORY _ID,
@COUNTER = 1
FROM INSERTED

IF @P_ID IS NOT NULL

BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
IF @TEMP_ID IS NULL
BREAK
END
END

UPDATE CATEGORY
SET DEPTH = @COUNTER

For this code, the trigger works fine if a category has a null
parent_categor_ id, but sql server hangs if there are values in the
parent_category _id, certainly because of the loop.

Coosa

Jul 23 '05 #8
i did, check my table and other replies from begin of this post

Jul 23 '05 #9
coosa (co*****@gmail. com) writes:
Beside, the trigger code is modfied now, still i run into hanging in
run time due too the loop.
Of course:
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
IF @TEMP_ID IS NULL
BREAK
END
INSERTED is not changing for the duration of the the trigger, so this is
indeed an infinite loop. You are reading the same ID over and over
again.

I repeat the request from the previous post:

The standard recomendation is that you post:

o CREATE TABLE statement for your table(s), possibly simplified.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective.

In another post, you said:
i did, check my table and other replies from begin of this post


CREATE TABLE was there, but the other points on my list are missing.
Of course, it is up to you, since you are the one who is asking for
help. If you prefer that people help you based only on guesses on
what you are trying to do and why, the quality of the response will
be in par with this. If we don't know what you want to do, we can't
tell you how to do it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1739
by: Michael Teja via SQLMonster.com | last post by:
I made a trigger for delete just like this. " CREATE TRIGGER ON . FOR DELETE AS Declare @severity int, @IdNmbr nvarchar(10) Set @Severity = 0
1
4334
by: Jen S | last post by:
I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION; RAISERROR('An error occurred in the stored proc.', 16, 1);
0
7143
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can manually call the stored proc, and the external trigger is created without any errors. However, when I do...
4
4176
by: Alexander Pope | last post by:
I am using db2 udb v8.2 AIX I have created trigger, however I am not confident it meets industry standards. If I make it fail, I cant tell from the message where it is failing. what can I add to trap the errors properly? CREATE TRIGGER myschema.t1_upd_t AFTER UPDATE OF dt ON myschema.t1 REFERENCING NEW AS N
12
4757
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.act_recov_date IS NOT NULL) BEGIN ATOMIC select count(*) into v_n from is3.flushes
5
3294
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is interpreted as: An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found
3
4947
by: ChrisN | last post by:
Hello all, I have a quick question. I'm using a C# object to commit new rows to a database. In the database I have an INSERT Trigger watching values come in. If the record to be committed fails the trigger's test, the trigger rolls back the INSERT command and no changes are made to the database. As far as my object is concerned, the transaction went through either way (no matter what the trigger did). What I need is for the object...
2
2060
by: Juan Jose Costello Levien | last post by:
Hello, I am trying to use a trigger function I wrote in C. Basically what I want to do is to audit a table when a row is inserted into another table by copying the row to the new table. It compiles Ok and I created a shared library trigger.so. But when I load it into pgAdmin it tells me 'An error had occured'. To address that I put here the source code so you can check it out: (note: it has a oid (blob) field the row, and that could be...
5
2640
by: Bruno Rafael Moreira de Barros | last post by:
function test1() { trigger_error('My error'); } application.php //code... test1(); //code...
11
7872
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist drop table log_errors_tab;
0
8725
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8644
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9214
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8924
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7807
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4403
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3088
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2027
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.