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.

Problem with Foreign Key ON UPDATE CASCADE & Triggers

5 posts in General Discussion Last posting was on 2005-10-11 15:36:07.0Z
Mariano Recuero Posted on 2005-10-10 14:24:18.0Z
Sender: 3cfd.4332db9b.1804289383@sybase.com
From: Mariano Recuero
Newsgroups: ianywhere.public.general
Subject: Problem with Foreign Key ON UPDATE CASCADE & Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <434a78df.68b2.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 10 Oct 2005 07:21:19 -0700, 10.22.241.42
Lines: 23
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Oct 2005 07:21:20 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Oct 2005 07:24:18 -0700
X-Trace: forums-1-dub 1128954258 10.22.108.75 (10 Oct 2005 07:24:18 -0700)
X-Original-Trace: 10 Oct 2005 07:24:18 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4845
Article PK: 17444

Hi,
I have a problem with changing values in a table.
This table, A, has a primary Key that is referenced by
another tables (foreign keys)
This tables have triggers for update and the FK are defined
as CASCADE ON UPDATE.
When i update a value in the PK of the table A, id like that
all the same values in the tables with foreign keys would
update with the new value.
But always shows me the same message Error: 'Select return
more than 1 row' .
It happens when the trigger of the tables is fired:
I have this code in the triggers:
Select @column1 = column1, @column2 = column2 from inserted
Id noticed that inserted has more than 2 rows, but it should
have only
1 row because the trigger is for each row.

Anyone can tell me something?
Is it possible make disabled the tirggers only for updating
from this table?

Thanks in Advance


Breck Carter [TeamSybase] Posted on 2005-10-10 16:08:25.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Problem with Foreign Key ON UPDATE CASCADE & Triggers
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <d94lk1t4ihssghk8o9ejqkoitovq8tlbrp@4ax.com>
References: <434a78df.68b2.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 10 Oct 2005 09:05:27 -0700, bcarter.sentex.ca
Lines: 38
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Oct 2005 09:05:28 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Oct 2005 09:08:25 -0700
X-Trace: forums-1-dub 1128960505 10.22.108.75 (10 Oct 2005 09:08:25 -0700)
X-Original-Trace: 10 Oct 2005 09:08:25 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4846
Article PK: 8575

What version and build are you using?

Please show us the code for the trigger.

Breck

On 10 Oct 2005 07:21:19 -0700, Mariano Recuero wrote:

>Hi,
>I have a problem with changing values in a table.
>This table, A, has a primary Key that is referenced by
>another tables (foreign keys)
>This tables have triggers for update and the FK are defined
>as CASCADE ON UPDATE.
>When i update a value in the PK of the table A, id like that
>all the same values in the tables with foreign keys would
>update with the new value.
>But always shows me the same message Error: 'Select return
>more than 1 row' .
>It happens when the trigger of the tables is fired:
>I have this code in the triggers:
>Select @column1 = column1, @column2 = column2 from inserted
>Id noticed that inserted has more than 2 rows, but it should
>have only
> 1 row because the trigger is for each row.
>
>Anyone can tell me something?
>Is it possible make disabled the tirggers only for updating
>from this table?
>
>Thanks in Advance

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Mariano Recuero Posted on 2005-10-11 12:52:08.0Z
Sender: 40fa.434bb39c.1804289383@sybase.com
From: Mariano Recuero
Newsgroups: ianywhere.public.general
Subject: Re: Problem with Foreign Key ON UPDATE CASCADE & Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <434bb578.4111.1681692777@sybase.com>
References: <d94lk1t4ihssghk8o9ejqkoitovq8tlbrp@4ax.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub434bb578"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Oct 2005 05:52:08 -0700
X-Trace: forums-1-dub 1129035128 10.22.241.41 (11 Oct 2005 05:52:08 -0700)
X-Original-Trace: 11 Oct 2005 05:52:08 -0700, 10.22.241.41
Lines: 175
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4849
Article PK: 17445

Version and Build: Adaptive Server Anywhere 9 9.0.1.1862

Code for the trigger in the attach file:

> What version and build are you using?
>
> Please show us the code for the trigger.
>
> Breck
>
> On 10 Oct 2005 07:21:19 -0700, Mariano Recuero wrote:
>
> >Hi,
> >I have a problem with changing values in a table.
> >This table, A, has a primary Key that is referenced by
> >another tables (foreign keys)
> >This tables have triggers for update and the FK are
> defined >as CASCADE ON UPDATE.
> >When i update a value in the PK of the table A, id like
> that >all the same values in the tables with foreign keys
> would >update with the new value.
> >But always shows me the same message Error: 'Select
> return >more than 1 row' .
> >It happens when the trigger of the tables is fired:
> >I have this code in the triggers:
> >Select @column1 = column1, @column2 = column2 from
> inserted >Id noticed that inserted has more than 2 rows,
> but it should >have only
> > 1 row because the trigger is for each row.
> >
> >Anyone can tell me something?
> >Is it possible make disabled the tirggers only for
> updating >from this table?
> >
> >Thanks in Advance
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

ALTER TRIGGER "tr_upd_cab_prescripcion_on" .tr_upd_cab_prescripcion_on on
dba.cab_prescripcion_on for update as
begin
/*
/ **********************************************************************************************************************

/
/ **********************************************************************************************************************
*/
declare @cab_prescripcion_on_id integer,
@status_on char(1),
@paciente_on_id char(10),
@cama_on_id char(7),
@paciente_en_esta_cama char(10),
@auditoria char(70),
@usuario char(20),
@fecha_administracion_hasta date,
@llamada_desde_traspaso_goblal char(1),
@countt integer
--
print '<<<<<<<<<<<<<<< Entro en el trigger de update de cab_prescripcion_on '
-- Inicializo variables
select @fecha_administracion_hasta=convert(date,today(*))
select @llamada_desde_traspaso_goblal='N'
--
print 'UPD CAB_PRESCRIPC_ON - 1'
select @countt = count (*) from inserted
print 'UPD CAB_PRESCRIPC_ON - COUNT INSERTED %1!', @countt
/* AFTER THIS IT MAKES WRONG, BECAUSE @countt > 1 and THE SELECT FOR
RETRIEVING VALUES FROM INSERTED SEND BACK MORE THAN 1 ROW*/
select @cab_prescripcion_on_id = cab_prescripcion_on_id,
@status_on = status_on,
@auditoria = inserted.auditoria from
inserted

print 'UPD CAB_PRESCRIPC_ON - 2'
-- *** PRIMERO LA AUDITORIA.
/*
Cambia la información de auditoria:
*/
select @usuario=@auditoria
select
@auditoria=@auditoria+' '+
convert(char(10),getdate(*),103)+' '+convert(char(8),getdate(*),108)+' ['
if update(cama_id) select @auditoria=@auditoria+'C'
if update(gfh_id) select @auditoria=@auditoria+'G'
if update(uh_id) select @auditoria=@auditoria+'U'
if update(paciente_id) select @auditoria=@auditoria+'P'
if update(medico_id) select @auditoria=@auditoria+'M'
-- if update(almacen_id) select @auditoria=@auditoria+'L'
if update(episodio) select @auditoria=@auditoria+'E'
if update(cab_observaciones) select @auditoria=@auditoria+'O'
-- if update(status_on) select @auditoria=@auditoria+'S'
if update(fecha_inicio) select @auditoria=@auditoria+'F'
if update(farmaceutico_id) select @auditoria=@auditoria+'A'
if update(diagnostico_id) select @auditoria=@auditoria+'D'
if update(localizacion_anatomica_id) select @auditoria=@auditoria+'O'
if update(histologia_id) select @auditoria=@auditoria+'H'
if update(estadio) select @auditoria=@auditoria+'T'
if update(tipo_dispensacion_individual_id) select @auditoria=@auditoria+'I'
if update(ok_avisomedico) select @auditoria=@auditoria+'K'
/* Auditoria al final""*/
print 'UPD CAB_PRESCRIPC_ON - 3'
update cab_prescripcion_on set
auditoria = @auditoria+']' where
cab_prescripcion_on_id = @cab_prescripcion_on_id
if @@error <> 0
begin
print 'ERROR tr_upd_cab_prescripcion_on: (CAB: %1!) No se pudo actualizar auditoria de cab_prescripcion_on.',
@cab_prescripcion_on_id
raiserror 17003 'tr_upd_cab_prescripcion_on: (CAB: %1!) No se pudo actualizar auditoria de cab_prescripcion_on.',
@cab_prescripcion_on_id
return
end

if update(status_on)
begin

if @status_on = 'S'
begin
print 'Hay que borrar las líneas de prescripcion con fecha de adm. mayor que la de hoy.'
delete from lin_prescripcion_on where cab_prescripcion_on_id = @cab_prescripcion_on_id and fecha_administracion > today(*)
if @@error <> 0
begin
print 'ERROR: Fallo en el borrado de lin_prescripcion_on en tr_upd_cab_prescripcion_on ID: %1!',
@cab_prescripcion_on_id
rollback transaction
raiserror 17000 ' Fallo en el borrado de lin_prescripcion_on en tr_upd_cab_prescripcion_on ID: %1!',
@cab_prescripcion_on_id
end
print 'Se ha dado de alta al paciente y voy a pasar a historico'
execute sp_on_traspaso_historico @cab_prescripcion_on_id,@status_on,@usuario,@fecha_administracion_hasta,
@llamada_desde_traspaso_goblal
print 'Ahora voy a borrar la cabecera de la tabla de cabeceras de prescripcion'
if(select count(*) from lin_prescripcion_on where cab_prescripcion_on_id = @cab_prescripcion_on_id) = 0
begin
delete from cab_prescripcion_on where cab_prescripcion_on_id = @cab_prescripcion_on_id
if @@error <> 0
begin
print 'ERROR: Fallo en el borrado de cab_prescripcion_on en tr_upd_cab_prescripcion_on ID: %1!',
@cab_prescripcion_on_id
rollback transaction
raiserror 17000 ' Fallo en el borrado de cab_prescripcion_on en tr_upd_cab_prescripcion_on ID: %1!',
@cab_prescripcion_on_id
end
print 'Ya he borrado la cabecera'
end
end
end
/
print '>>>>>>>>>>> Salgo del trigger de update de cab_prescripcion_on '
end


Breck Carter [TeamSybase] Posted on 2005-10-11 14:04:59.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Problem with Foreign Key ON UPDATE CASCADE & Triggers
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <dehnk19vkf1ta2nn6jm80mno4mi1r0l125@4ax.com>
References: <d94lk1t4ihssghk8o9ejqkoitovq8tlbrp@4ax.com> <434bb578.4111.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 11 Oct 2005 07:04:59 -0700
X-Trace: forums-1-dub 1129039499 64.7.134.118 (11 Oct 2005 07:04:59 -0700)
X-Original-Trace: 11 Oct 2005 07:04:59 -0700, bcarter.sentex.ca
Lines: 73
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4850
Article PK: 8576

This is a trigger written in the Transact SQL dialect:

ALTER TRIGGER "tr_upd_cab_prescripcion_on" .tr_upd_cab_prescripcion_on
on
dba.cab_prescripcion_on for update as
begin

This kind of trigger is a *statement*-level trigger, not a row-level
trigger, so if the UPDATE statement affects 2 rows, you will see 2
rows in both the inserted and updated temporary tables.

Triggers written in the Watcom SQL dialect are much more flexible; see
the Help for more information, or section 8.11 in my book.

Breck

On 11 Oct 2005 05:52:08 -0700, Mariano Recuero wrote:

>Version and Build: Adaptive Server Anywhere 9 9.0.1.1862
>
>Code for the trigger in the attach file:
>
>
>
>
>> What version and build are you using?
>>
>> Please show us the code for the trigger.
>>
>> Breck
>>
>> On 10 Oct 2005 07:21:19 -0700, Mariano Recuero wrote:
>>
>> >Hi,
>> >I have a problem with changing values in a table.
>> >This table, A, has a primary Key that is referenced by
>> >another tables (foreign keys)
>> >This tables have triggers for update and the FK are
>> defined >as CASCADE ON UPDATE.
>> >When i update a value in the PK of the table A, id like
>> that >all the same values in the tables with foreign keys
>> would >update with the new value.
>> >But always shows me the same message Error: 'Select
>> return >more than 1 row' .
>> >It happens when the trigger of the tables is fired:
>> >I have this code in the triggers:
>> >Select @column1 = column1, @column2 = column2 from
>> inserted >Id noticed that inserted has more than 2 rows,
>> but it should >have only
>> > 1 row because the trigger is for each row.
>> >
>> >Anyone can tell me something?
>> >Is it possible make disabled the tirggers only for
>> updating >from this table?
>> >
>> >Thanks in Advance
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
>>
>http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Mariano Recuero Posted on 2005-10-11 15:36:07.0Z
Sender: 732a.434bdae3.1804289383@sybase.com
From: Mariano Recuero
Newsgroups: ianywhere.public.general
Subject: Re: Problem with Foreign Key ON UPDATE CASCADE & Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <434bdb2f.7333.1681692777@sybase.com>
References: <dehnk19vkf1ta2nn6jm80mno4mi1r0l125@4ax.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 11 Oct 2005 08:33:03 -0700, 10.22.241.42
Lines: 81
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 11 Oct 2005 08:33:04 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 11 Oct 2005 08:36:07 -0700
X-Trace: forums-1-dub 1129044967 10.22.108.75 (11 Oct 2005 08:36:07 -0700)
X-Original-Trace: 11 Oct 2005 08:36:07 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4851
Article PK: 17447

Thanks very much.

Id just bought your boork ;)

> This is a trigger written in the Transact SQL dialect:
>
> ALTER TRIGGER "tr_upd_cab_prescripcion_on"
> tr_upd_cab_prescripcion_on on
> dba.cab_prescripcion_on for update as
> begin
>
> This kind of trigger is a *statement*-level trigger, not a
> row-level trigger, so if the UPDATE statement affects 2
> rows, you will see 2 rows in both the inserted and updated
> temporary tables.
>
> Triggers written in the Watcom SQL dialect are much more
> flexible; see the Help for more information, or section
> 8.11 in my book.
>
> Breck
>
>
> On 11 Oct 2005 05:52:08 -0700, Mariano Recuero wrote:
>
> >Version and Build: Adaptive Server Anywhere 9
> 9.0.1.1862 >
> >Code for the trigger in the attach file:
> >
> >
> >
> >
> >> What version and build are you using?
> >>
> >> Please show us the code for the trigger.
> >>
> >> Breck
> >>
> >> On 10 Oct 2005 07:21:19 -0700, Mariano Recuero wrote:
> >>
> >> >Hi,
> >> >I have a problem with changing values in a table.
> >> >This table, A, has a primary Key that is referenced
> by >> >another tables (foreign keys)
> >> >This tables have triggers for update and the FK are
> >> defined >as CASCADE ON UPDATE.
> >> >When i update a value in the PK of the table A, id
> like >> that >all the same values in the tables with
> foreign keys >> would >update with the new value.
> >> >But always shows me the same message Error: 'Select
> >> return >more than 1 row' .
> >> >It happens when the trigger of the tables is fired:
> >> >I have this code in the triggers:
> >> >Select @column1 = column1, @column2 = column2 from
> >> inserted >Id noticed that inserted has more than 2 rows
> , >> but it should >have only
> >> > 1 row because the trigger is for each row.
> >> >
> >> >Anyone can tell me something?
> >> >Is it possible make disabled the tirggers only for
> >> updating >from this table?
> >> >
> >> >Thanks in Advance
> >>
> >> --
> >> SQL Anywhere Studio 9 Developer's Guide
> >> Buy the book:
> >>
> >http://www.amazon.com/exec/obidos/ASIN/1556225067/risingr
> oad-20 >> bcarter@risingroad.com
> >> RisingRoad SQL Anywhere and MobiLink Professional
> Services >> www.risingroad.com
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com