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