topic Re: Update query in Operating System - HP-UX

Update query

Armando Hernandez — Tue, 29 Sep 2009 17:27:36 GMT

I would like to clear some fields from the BUG table that are auto populated so that they do not appear on the email template that is sent out.

I would like to have this statement execute after new defects have been entered. Please help.

My SQL statement is:

UPDATE REGULATORY_UPDATE.BUG
SET BG_REPRODUCIBLE = ' ', BG_DETECTED_BY= ' '

Re: Update query

Jean-Luc Oudart — Wed, 30 Sep 2009 09:25:32 GMT

Armando

sounds like you need a trigger on this table.
see associated documentation
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#SQLRF01405

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ADFNS012

In essence you can blank these fields as rows are inserted into the "REGULATORY_UPDATE" table.

Regards
Jean-Luc

Re: Update query

Eric Antunes — Wed, 30 Sep 2009 10:57:07 GMT

Hi,

Create a trigger like this:

CREATE OR REPLACE TRIGGER BUG_BI
before insert on REGULATORY_UPDATE.BUG
referencing new as new
old as old
for each row

begin
:new.BG_REPRODUCIBLE = null;
:new.BG_DETECTED_BY = null;
end;

Re: Update query

Armando Hernandez — Wed, 30 Sep 2009 15:37:48 GMT

CREATE or REPLACE TRIGGER BUG_BI
before insert ON REGULATORY_UPDATE.BUG
WHEN new as null
old as Not Null
for each row

begin
:new.BG_REPRODUCIBLE = null;
:new.BG_DETECTED_BY = null;
end;
/

When I enter this into the HP Quality Center workflow - Script editor engine it highlights the first line and gives me a message of [Error] (L492: c8): Syntax error

All I am trying to do here and I am sure guys that it is my fault but I am not getting. I appreciate your patience...

I have a table called BUGS in this table there are two fields called BG_DETECTED_BY and BG_REPRODUCIBLE that get auto populated. I wish to make these two fields blank or null after it is updated. How can I do this. Most of the code in this Workflow - Script Editor seems to be VBScript.

Please help... Again thank you for your patience..

Re: Update query

Jean-Luc Oudart — Fri, 02 Oct 2009 08:52:03 GMT

Armando

there are errors in the syntax.
use the show error command to indicate the line and error.

SQL> show error
Errors for TRIGGER BUG_BI:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ; indicator

the trigger should look like this :
CREATE OR REPLACE TRIGGER BUG_BI
before insert on XXXX.BGTEST
referencing new as new
old as old
for each row
begin
:new.BG_REG := null;
:new.BG_REASON := null;
end;

/

Note := instead of equal sign.

Regards
Jean-Luc

Re: Update query

Suraj K Sankari — Fri, 02 Oct 2009 09:13:52 GMT

Hi Armando Hernandez,

Please don't mind this ITRC site is for HP server related issue.

Suraj