Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Trigger SQL

3 posts in General Discussion Last posting was on 2003-07-16 13:02:29.0Z
florence Posted on 2003-07-16 04:30:44.0Z
From: Florence
Date: Wed, 16 Jul 2003 00:30:44 -0400
Newsgroups: ianywhere.public.general
Subject: Trigger SQL
Message-ID: <DDEDCB31288E36280018C98985256D65.0018C9A485256D65@webforums>
Lines: 18
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:1347
Article PK: 3583

I have created a trigger for insert all column values to [CycleTime] in
each insertion of [TempMain] table. The statement is:

CREATE TRIGGER DBA.insertupdate AFTER INSERT ON DBA.TempMain REFERENCING
OLD AS TempMain NEW AS CycleTime
FOR EACH ROW
BEGIN
INSERT INTO CycleTime(No_of_min,dTime,MachID,AV,SV,Indice) VALUES
(:old.No_of_min,:old.dTime,:old.MachID,:old.AV,:old.SV,:old.Indice)

After that, I insert a row into [TempMain] using this statement, INSERT
INTO TempMain (No_of_min,dTime,MachID,AV,SV,Indice) VALUES
('1','2003-1-1','1','10','10','1000')
But it prompts up an error message says "Not enough values for host
variables".

Anything wrong with my statement? Thanks!

Florence


Bruce Hay Posted on 2003-07-16 12:55:46.0Z
From: "Bruce Hay" <ns_hay@sybase.com>
References: <DDEDCB31288E36280018C98985256D65.0018C9A485256D65@webforums>
Subject: Re: Trigger SQL
Date: Wed, 16 Jul 2003 08:55:46 -0400
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <ujGVBr5SDHA.349@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: hay-xp.sybase.com 172.31.142.57
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1352
Article PK: 3587

Change all occurrences of ":old." in the trigger to "TempMain.".

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Florence> wrote in message
news:DDEDCB31288E36280018C98985256D65.0018C9A485256D65@webforums...
> I have created a trigger for insert all column values to [CycleTime] in
> each insertion of [TempMain] table. The statement is:
>
> CREATE TRIGGER DBA.insertupdate AFTER INSERT ON DBA.TempMain REFERENCING
> OLD AS TempMain NEW AS CycleTime
> FOR EACH ROW
> BEGIN
> INSERT INTO CycleTime(No_of_min,dTime,MachID,AV,SV,Indice) VALUES
> (:old.No_of_min,:old.dTime,:old.MachID,:old.AV,:old.SV,:old.Indice)
>
> After that, I insert a row into [TempMain] using this statement, INSERT
> INTO TempMain (No_of_min,dTime,MachID,AV,SV,Indice) VALUES
> ('1','2003-1-1','1','10','10','1000')
> But it prompts up an error message says "Not enough values for host
> variables".
>
> Anything wrong with my statement? Thanks!
>
> Florence


Reg Domaratzki Posted on 2003-07-16 13:02:29.0Z
From: "Reg Domaratzki" <rdomarat__AT__ianywhere__DOT__com>
References: <DDEDCB31288E36280018C98985256D65.0018C9A485256D65@webforums>
Subject: Re: Trigger SQL
Date: Wed, 16 Jul 2003 09:02:29 -0400
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <O3LYxu5SDHA.298@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: rdomarat-pc.sybase.com 172.31.143.163
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1353
Article PK: 3588

The "REFERENCING NEW AS CycleTime" clause is meant to identify the row being
inserted. You don't use host variables in the insert, but reference the new
row. I've re-written your trigger, and renamed the new row, since it's a
little confusing when your table name is the same as your new row.

CREATE TRIGGER DBA.insertupdate AFTER INSERT ON DBA.TempMain
REFERENCING NEW AS new_row FOR EACH ROW
BEGIN
INSERT INTO CycleTime(No_of_min,dTime,MachID,AV,SV,Indice)
VALUES
(new_row.No_of_min,new_row.dTime,new_row.MachID,new_row.AV,new_row.SV,new_ro
w.Indice)
END;

PS : There is no "old row" in the an insert trigger, so I removed that
clause.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Florence> wrote in message
news:DDEDCB31288E36280018C98985256D65.0018C9A485256D65@webforums...
> I have created a trigger for insert all column values to [CycleTime] in
> each insertion of [TempMain] table. The statement is:
>
> CREATE TRIGGER DBA.insertupdate AFTER INSERT ON DBA.TempMain REFERENCING
> OLD AS TempMain NEW AS CycleTime
> FOR EACH ROW
> BEGIN
> INSERT INTO CycleTime(No_of_min,dTime,MachID,AV,SV,Indice) VALUES
> (:old.No_of_min,:old.dTime,:old.MachID,:old.AV,:old.SV,:old.Indice)
>
> After that, I insert a row into [TempMain] using this statement, INSERT
> INTO TempMain (No_of_min,dTime,MachID,AV,SV,Indice) VALUES
> ('1','2003-1-1','1','10','10','1000')
> But it prompts up an error message says "Not enough values for host
> variables".
>
> Anything wrong with my statement? Thanks!
>
> Florence