Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Update query

Armando Hernandez
Occasional Visitor

Update query

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= ' '
5 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Update query

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
fiat lux
Eric Antunes
Honored Contributor

Re: Update query

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;
Each and every day is a good day to learn.
Armando Hernandez
Occasional Visitor

Re: Update query

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..
Jean-Luc Oudart
Honored Contributor

Re: Update query

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
fiat lux
Suraj K Sankari
Honored Contributor

Re: Update query

Hi Armando Hernandez,

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

Suraj