Ok
We have a table that it's the HIST_TABLE of other 19 tables (the data it's denormalized into this table)
Ok... You can say... Why denormalize the 19 tables?
Because, we have few indexes... No FK's... Less space occupied, etc.
But we have a BEFORE insert TRIGGER that doesn't allow to duplicate data...
So I think with this trigger... You can learn how to create INSERT TRIGGERS
CREATE OR REPLACE TRIGGER SIEB_HIST_COMMENTS_NULL
BEFORE INSERT
ON HIST_SERVICE_ORDERS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
counter NUMBER;
/******************************************************************************
NAME:
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 16-04-2004 1. Created this trigger.
NOTES:
Automatically available Auto Replace Keywords:
Object Name:
Sysdate: 16-04-2004
Date and Time: 16-04-2004, 15:28:59, and 16-04-2004 15:28:59
Username: Performance Team
Table Name:
Trigger Options: To reduce comments and con_name to mininum disk usage
******************************************************************************/
BEGIN
counter := 0;
-- check if exists at least one record in table
select count(1) into counter from hist_service_orders where
so_row_id=:new.so_row_id and :new.con_name is not null and rownum<2;
-- if exists one... then fields will not be duplicated
if counter=1 then
:new.con_name:='';
:new.so_comments:='';
end if;
EXCEPTION
-- First Values
WHEN OTHERS THEN
null;
END;
Luís Ganhão
-----Original Message-----
From: Patel Daxa [mailto:[Email address protected]
Sent: segunda-feira, 15 de Novembro de 2004 15:14
To: Ganhão, Luís Manuel Martins, VF-PT
Subject: RE: TRIGGER
It's column of my table and I am new in this field so
If knew pattern I can write by myself Thanks
-----Original Message-----
From: "Ganhão, Luís Manuel Martins, VF-PT"
[mailto:luis.[Email address protected]
Sent: Monday, November 15, 2004 10:06 AM
To: Patel Daxa
Subject: RE: TRIGGER
**A LazyDBA.com subscriber has responded to your
lazydba.com post** **LazyDBA.com mail shield has
forwarded you this email,
**and removed any attachments, and kept your email
address secret **from this person, and any viruses/trojans.
**If you reply to this email, the person will see
your email address as normal
**Anything below this line is the original email text
Why don't you search on the web?
Your question doesn't make sense...
Your table with this code will not be created...
What do you want to do with your trigger?
Please, be more specific!!!!!
-----Original Message-----
From: Patel Daxa
[mailto:oracledba-ezmlmshield-x56168973.[Email Address Removed] zyDBA.com]
Sent: segunda-feira, 15 de Novembro de 2004 15:08
To: LazyDBA Discussion
Subject: RE: TRIGGER
Hi
Here is my table and I want to write Trigger on
insert or update.. Can any body help me? Thanks in
advance Patel
CREATE TABLE QICORE.QIC_workorder
(
request_id number,
OBJECT_NAME VARCHAR2(100),
object_type VARCHAR2(100),
request_DATE DATE,
REQUEST_BY VARCHAR2(100),
ACTION VARCHAR2(100),
OLD_VALUE VARCHAR2(100),
NEW_VALUE VARCHAR2(100),
TEST_DB CHAR(6),
PROD_DB CHAR(6),
COMPLETION_DATE DATE,
COMPLETION_BY VARCHAR2(100),
QA_DATE DATE,
QA_BY VARCHAR2(100),
SYNCHRO_DATE DATE,
SYNCHRO_BY VARCHAR2(100),
APP_VERSION VARCHAR2(100),
COMMENTS VARCHAR2(400),
)
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here:
http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
------------------------------------------
This message may contain confidential information or privileged material,
and is intended only for the individual(s) named. If you are not in the
named addressee you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late
or incomplete, or contain viruses. The sender therefore does not accept
liability for any errors or omissions in the contents of this message
which arise as a result of e-mail transmission. If verification is
required please request a hard-copy version.
Vodafone (Portugal)
Oracle LazyDBA home page