Home » SQL & PL/SQL » SQL & PL/SQL » trigger (oracle 10g)
trigger [message #391383] |
Thu, 12 March 2009 00:52  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Hi,
i want to create a trigger so that if i delete some records from table 1 that delete ones should be inserted into the second table
based on some conditions
like table1: EMRLABTEMPLONIC
columns are like
1)test_class_type which is numeric and values inserted all are 1
2)test-final which is char and values insertes all are Y.
now iam having another table like EMRLOCATIONMASTER with same structure with no records in it.
now my conditions are like this
a) if test_class_type<> 1 it should not move records into another table
b) if test_class_type =1 and test_final<>y then records should not be moved into other
create or replace trigger tr_delete_EMRLABTEMPLONIC after delete
on EMRLABTEMPLONIC for each row
can you tell me how to proceed for this
|
|
|
|
Re: trigger [message #391394 is a reply to message #391383] |
Thu, 12 March 2009 01:45   |
|
Quote: |
1)test_class_type which is numeric and values inserted all are 1
2)test-final which is char and values insertes all are Y.
|
When you always have 1 and Y into the respective columns...why do you need ?
Quote: |
a) if test_class_type<> 1 it should not move records into another table
b) if test_class_type =1 and test_final<>y then records should not be moved into other
|
And hey, post whatever you have tried so far.
Regards,
Ashoka BL
|
|
|
Re: trigger [message #391411 is a reply to message #391394] |
Thu, 12 March 2009 03:38   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
my trigger is like this
CREATE OR REPLACE TRIGGER TR_Delete_EMRLABTEMPLOINC AFTER DELETE ON EMRLABTEMPLOINC
FOR EACH ROW
WHEN (TEST_CLASSSTYPE<>1) OR (TEST_CLASSTYPE=1 AND TEST_FINAL<>'Y')
BEGIN
INSERT INTO EMRLONICMASTER
(TEST_LOINC_NUM,TEST_COMPONENT,TEST_PROPERTY,TEST_TIME_ASPCT,TEST_SYSTEM,TEST_TIME_ASPCT,TEST_SYSTEM,TEST_SCALE_TYP,TEST_METHOD_TYP,
TEST_RELAT_NMS,TEST_CLASS,TEST_SOURCE,TEST_DT_LAST_CH,TEST_CHNG_TYPE,TEST_COMMENTS,TEST_ANSWERLIST, TEST_STATUS,TEST_MAP_TO,
TEST_SCOPE,TEST_NORM_RANGE,TEST_IPCC_UNITS,TEST_REFERENCE,TEST_EXACT_CMP_SY,TEST_MOLAR_MASS,TEST_CLASSTYPE,TEST_FORMULA,TEST_SPECIES,
TEST_EXMPL_ANSWERS,TEST_ACSSYM,TEST_BASE_NAME,TEST_FINAL,TEST_NAACCR_ID,TEST_CODE_TABLE,TEST_SETROOT,TEST_PANELELEMENTS,
TEST_SURVEY_QUEST_TEXT,TEST_SURVEY_QUEST_SRC,TEST_SURVEY_QUEST_SRC,TEST_UNITSREQUIRED,TEST_SUBMITTED_UNITS,TEST_RELATEDNAMES2,
TEST_SHORTNAME,TEST_ORDER_OBS,TEST_CDISC_COMMON_TESTS,TEST_HL7_FIELD_SUBFIELD_ID,TEST_EXTERNAL_COPYRIGHT_NOTICE,TEST_EXAMPLE_UNITS,
TEST_INPC_PERCENTAGE,TEST_LONG_COMMON_NAME)
VALUES
(:old.TEST_LOINC_NUM, :old.TEST_COMPONENT, :old.TEST_PROPERTY, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_SCALE_TYP,:old.TEST_METHOD_TYP,
:old.TEST_RELAT_NMS,:old.TEST_CLASS, :old.TEST_SOURCE, :old.TEST_DT_LAST_CH, :old.TEST_CHNG_TYPE,:old.TEST_COMMENTS,:old.TEST_ANSWERLIST,:old.TEST_STATUS,:old.TEST_MAP_TO,
:old.TEST_SCOPE,:old.TEST_NORM_RANGE,:old.TEST_IPCC_UNITS,:old.TEST_REFERENCE,:old.TEST_EXACT_CMP_SY,:old.TEST_MOLAR_MASS,:old.TEST_C LASSTYPE,:old.TEST_FORMULA,:old.TEST_SPECIES,
:old.TEST_EXMPL_ANSWERS,:old.TEST_ACSSYM,:old.TEST_BASE_NAME,:old.TEST_FINAL,:old.TEST_NAACCR_ID,:old.TEST_CODE_TABLE,:old.TEST_SETRO OT,:old.TEST_PANELELEMENTS,
:old.TEST_SURVEY_QUEST_TEXT,:old.TEST_SURVEY_QUEST_SRC,:old.TEST_SURVEY_QUEST_SRC,:old.TEST_UNITSREQUIRED,:old.TEST_SUBMITTED_UNITS,: old.TEST_RELATEDNAMES2,
:old.TEST_SHORTNAME,:old.TEST_ORDER_OBS,:old.TEST_CDISC_COMMON_TESTS,:old.TEST_HL7_FIELD_SUBFIELD_ID,:old.TEST_EXTERNAL_COPYRIGHT_NOT ICE,:old.TEST_EXAMPLE_UNITS,
:old.TEST_INPC_PERCENTAGE,:old.TEST_LONG_COMMON_NAME);
END TR_DELETE_EMRLABTEMPLOINC;
/
both the conditions required for moving records into another table but while executing trigger iam getting exception as
ORA-04076: invalid NEW or OLD specification
|
|
|
|
Re: trigger [message #391416 is a reply to message #391412] |
Thu, 12 March 2009 04:01   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
ashoka_bl wrote on Thu, 12 March 2009 09:42 | come on man...138 messages,senior member....FORMAT the code atleast once and send it...
|
Don't you see my post in his previous topic? (I wonder why I still try to help him):
Michel Cadot wrote on Tue, 10 March 2009 07:55 | From your previous topic:
Michel Cadot wrote on Tue, 10 March 2009 06:13 | From one of your previous topic:
Michel Cadot wrote on Mon, 23 February 2009 08:11 | Michel Cadot wrote on Mon, 23 February 2009 07:49 | From one of your previous topic with the same function:
Michel Cadot wrote on Tue, 17 February 2009 10:20 | From your previous posts:
BlackSwan wrote on Tue, 17 February 2009 05:35 | You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.
Yes, if you are willing to Read The Fine Manual.
|
BlackSwan wrote on Tue, 10 February 2009 05:58 | >iam getting exceptions
Exceptions? What exceptions? I don't see any problem.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
|
Michel Cadot wrote on Tue, 10 February 2009 07:55 | From your previous topic:
Michel Cadot wrote on Mon, 09 February 2009 07:28 | Copy and paste EXACTLY what you did.
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
Which the same one than this one, so I merged one and the following is still true:
Michel Cadot wrote on Mon, 09 February 2009 12:32 | You still didn't post what I asked and how I asked it.
Regards
Michel
|
|
And so on.
Regards
Michel
|
|
|
|
|
|
|
|
|
Re: trigger [message #391421 is a reply to message #391416] |
Thu, 12 March 2009 04:10   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: | Don't you see my post in his previous topic? (I wonder why I still try to help him):
|
I think you're being a bit too generous to yourself if you call that post 'help'
|
|
|
Re: trigger [message #391423 is a reply to message #391420] |
Thu, 12 March 2009 04:13   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You need to refer to variables in the WHEN clauses as either NEW.variable or OLD.variable (note: not :NEW.variable)
In your case, with an ON DELETE trigger, they'll all be OLD.variable
|
|
|
|
|
|
Re: trigger [message #391431 is a reply to message #391411] |
Thu, 12 March 2009 04:32   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@rajasekhar857,
You are not getting an exception, you are getting an error while creating the trigger.
From Oracle Documentation |
WHEN Clause
Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. See the syntax description of condition in Chapter 6, " Conditions". This condition must contain correlation names and cannot contain a query.
The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables, so are not preceded by a colon ( : ). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause.
|
Correct it.
[Ah... Thanks to my speedy connection or OraFAQ's response time I didn't see any topic after Michel's... Seems OP figured it out]
Regards,
Jo
[Updated on: Thu, 12 March 2009 04:35] Report message to a moderator
|
|
|
Re: trigger [message #391470 is a reply to message #391424] |
Thu, 12 March 2009 06:04   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
CREATE OR REPLACE TRIGGER TR_Delete_test1 AFTER DELETE ON test1
FOR EACH ROW
BEGIN
IF (TEST_CLASSSTYPE<>1) OR (TEST_CLASSTYPE=1 AND TEST_FINAL<>'Y') THEN
INSERT INTO test2(
TEST_LOINC_NUM,TEST_COMPONENT, TEST_PROPERTY, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_SCALE_TYP, TEST_METHOD_TYP,
TEST_RELAT_NMS,TEST_CLASS, TEST_SOURCE, TEST_DT_LAST_CH, TEST_CHNG_TYPE, TEST_COMMENTS, TEST_ANSWERLIST, TEST_STATUS, TEST_MAP_TO,
TEST_SCOPE, TEST_NORM_RANGE, TEST_IPCC_UNITS, TEST_REFERENCE, TEST_EXACT_CMP_SY, TEST_MOLAR_MASS, TEST_CLASSTYPE, TEST_FORMULA, TEST_SPECIES,
TEST_EXMPL_ANSWERS, TEST_ACSSYM, TEST_BASE_NAME, TEST_FINAL, TEST_NAACCR_ID, TEST_CODE_TABLE, TEST_SETROOT, TEST_PANELELEMENTS,
TEST_SURVEY_QUEST_TEXT, TEST_SURVEY_QUEST_SRC, TEST_SURVEY_QUEST_SRC, TEST_UNITSREQUIRED, TEST_SUBMITTED_UNITS, TEST_RELATEDNAMES2,
TEST_SHORTNAME, TEST_ORDER_OBS, TEST_CDISC_COMMON_TESTS, TEST_HL7_FIELD_SUBFIELD_ID, TEST_EXTERNAL_COPYRIGHT_NOTICE, TEST_EXAMPLE_UNITS,
TEST_INPC_PERCENTAGE, TEST_LONG_COMMON_NAME)
VALUES
(:old.TEST_LOINC_NUM, :old.TEST_COMPONENT, :old.TEST_PROPERTY, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_SCALE_TYP, :old.TEST_METHOD_TYP,
:old.TEST_RELAT_NMS, :old.TEST_CLASS, :old.TEST_SOURCE, :old.TEST_DT_LAST_CH, :old.TEST_CHNG_TYPE, :old.TEST_COMMENTS, :old.TEST_ANSWERLIST, :old.TEST_STATUS, :old.TEST_MAP_TO,
:old.TEST_SCOPE, :old.TEST_NORM_RANGE, :old.TEST_IPCC_UNITS, :old.TEST_REFERENCE, :old.TEST_EXACT_CMP_SY, :old.TEST_MOLAR_MASS, :old.TEST_CLASSTYPE, :old.TEST_FORMULA ,:old.TEST_SPECIES,
:old.TEST_EXMPL_ANSWERS, :old.TEST_ACSSYM, :old.TEST_BASE_NAME, :old.TEST_FINAL, :old.TEST_NAACCR_ID, :old.TEST_CODE_TABLE, :old.TEST_SETROOT, :old.TEST_PANELELEMENTS,
:old.TEST_SURVEY_QUEST_TEXT, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_UNITSREQUIRED, :old.TEST_SUBMITTED_UNITS, :old.TEST_RELATEDNAMES2,
:old.TEST_SHORTNAME, :old.TEST_ORDER_OBS, :old.TEST_CDISC_COMMON_TESTS, :old.TEST_HL7_FIELD_SUBFIELD_ID, :old.TEST_EXTERNAL_COPYRIGHT_NOTICE, :old.TEST_EXAMPLE_UNITS,
:old.TEST_INPC_PERCENTAGE, :old.TEST_LONG_COMMON_NAME);
END IF;
END TR_DELETE_test1;
/
|
|
|
Re: trigger [message #391472 is a reply to message #391470] |
Thu, 12 March 2009 06:14   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@rajasekhar857,
You have to place your codes in between the [code] tags.
Anyways, is the trigger working as you expected?
Regards,
Jo
|
|
|
Re: trigger [message #391474 is a reply to message #391383] |
Thu, 12 March 2009 06:19   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
IF (TEST_CLASSSTYPE<>1) OR (TEST_CLASSTYPE=1 AND TEST_FINAL<>'Y') THEN
Are they supposed to be old values or new values?
|
|
|
|
|
|
Re: trigger [message #391497 is a reply to message #391489] |
Thu, 12 March 2009 06:52   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
yeah iam unable to do it once again. the trigger is invalid message is been coming.iam unable to delete rows from table 1 and insert the deleted rows from table 2.why it is happening
|
|
|
Re: trigger [message #391500 is a reply to message #391383] |
Thu, 12 March 2009 06:58   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If the items in the IF statement are supposed to be the old values then you need to rewrite the IF statement to specifiy that they're old values.
|
|
|
|
Re: trigger [message #391506 is a reply to message #391504] |
Thu, 12 March 2009 07:16   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@rajasekhar857,
Please prove what you have claimed by posting the SQL Session like below:
SQL> CREATE OR REPLACE TRIGGER TR_Delete_TEST_TAB AFTER DELETE ON test_tab
2 FOR EACH ROW
3 BEGIN
4 IF (:old.Col_2 <> 1) OR (:old.Col_1=1 AND :old.Col_1<>'D') THEN
5 INSERT INTO test_tab_new(Col_1, Col_2) values
6 SELECT :old.Col_1, :old.col_2 FROM test_tab;
7 END IF;
8 END TR_Delete_TEST_TAB;
9 /
Warning: Trigger created with compilation errors.
SQL> show errors;
Errors for TRIGGER TR_DELETE_TEST_TAB:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/47 PL/SQL: ORA-00936: missing expression
PS: Included errors intentionally for OP to understand.
Regards,
Jo
|
|
|
Re: trigger [message #391507 is a reply to message #391506] |
Thu, 12 March 2009 07:21   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
CREATE OR REPLACE TRIGGER TR_Delete_T1 AFTER DELETE ON T1
FOR EACH ROW
BEGIN
IF (:old.TEST_CLASSSTYPE<>1) OR (:old.TEST_CLASSTYPE=1 AND :old.TEST_FINAL<>'Y') THEN
INSERT INTO T2 (
TEST_LOINC_NUM,TEST_COMPONENT, TEST_PROPERTY, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_SCALE_TYP, TEST_METHOD_TYP,
TEST_RELAT_NMS,TEST_CLASS, TEST_SOURCE, TEST_DT_LAST_CH, TEST_CHNG_TYPE, TEST_COMMENTS, TEST_ANSWERLIST, TEST_STATUS, TEST_MAP_TO,
TEST_SCOPE, TEST_NORM_RANGE, TEST_IPCC_UNITS, TEST_REFERENCE, TEST_EXACT_CMP_SY, TEST_MOLAR_MASS, TEST_CLASSTYPE, TEST_FORMULA, TEST_SPECIES,
TEST_EXMPL_ANSWERS, TEST_ACSSYM, TEST_BASE_NAME, TEST_FINAL, TEST_NAACCR_ID, TEST_CODE_TABLE, TEST_SETROOT, TEST_PANELELEMENTS,
TEST_SURVEY_QUEST_TEXT, TEST_SURVEY_QUEST_SRC, TEST_SURVEY_QUEST_SRC, TEST_UNITSREQUIRED, TEST_SUBMITTED_UNITS, TEST_RELATEDNAMES2,
TEST_SHORTNAME, TEST_ORDER_OBS, TEST_CDISC_COMMON_TESTS, TEST_HL7_FIELD_SUBFIELD_ID, TEST_EXTERNAL_COPYRIGHT_NOTICE, TEST_EXAMPLE_UNITS,
TEST_INPC_PERCENTAGE, TEST_LONG_COMMON_NAME)
VALUES
(:old.TEST_LOINC_NUM, :old.TEST_COMPONENT, :old.TEST_PROPERTY, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_SCALE_TYP, :old.TEST_METHOD_TYP,
:old.TEST_RELAT_NMS, :old.TEST_CLASS, :old.TEST_SOURCE, :old.TEST_DT_LAST_CH, :old.TEST_CHNG_TYPE, :old.TEST_COMMENTS, :old.TEST_ANSWERLIST, :old.TEST_STATUS, :old.TEST_MAP_TO,
:old.TEST_SCOPE, :old.TEST_NORM_RANGE, :old.TEST_IPCC_UNITS, :old.TEST_REFERENCE, :old.TEST_EXACT_CMP_SY, :old.TEST_MOLAR_MASS, :old.TEST_CLASSTYPE, :old.TEST_FORMULA ,:old.TEST_SPECIES,
:old.TEST_EXMPL_ANSWERS, :old.TEST_ACSSYM, :old.TEST_BASE_NAME, :old.TEST_FINAL, :old.TEST_NAACCR_ID, :old.TEST_CODE_TABLE, :old.TEST_SETROOT, :old.TEST_PANELELEMENTS,
:old.TEST_SURVEY_QUEST_TEXT, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_UNITSREQUIRED, :old.TEST_SUBMITTED_UNITS, :old.TEST_RELATEDNAMES2,
:old.TEST_SHORTNAME, :old.TEST_ORDER_OBS, :old.TEST_CDISC_COMMON_TESTS, :old.TEST_HL7_FIELD_SUBFIELD_ID, :old.TEST_EXTERNAL_COPYRIGHT_NOTICE, :old.TEST_EXAMPLE_UNITS,
:old.TEST_INPC_PERCENTAGE, :old.TEST_LONG_COMMON_NAME);
END IF;
END TR_DELETE_T1;
/
trigger created
delete from t1;
EXCEPTION: trigger is invalid
|
|
|
Re: trigger [message #391509 is a reply to message #391507] |
Thu, 12 March 2009 07:26   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
iam having table t1 with data like this.
10584-1 Protein MCnc Pt Smpls Qn FERT IUPAC 19981217 DEL EMPTY_CLOB() EMPTY_CLOB() DEL 2884-5 EMPTY_CLOB() 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() g/L ; Prot; PR; Mass concentration; Level; Point in time; Random; Quantitative; QNT; Quant; Quan; FERTILITY TESTING EMPTY_CLOB() 0.0
1058-7 I Ab ACnc Pt Ser/Plas^BPU Ord BLDBK FS 20020722 MAJ EMPTY_CLOB() EMPTY_CLOB() EMPTY_CLOB() I AB 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() Arbitrary concentration; Point in time; Random; SerPl; SerPlas; SerP; Serum; SR; Plasma; Pl; Plsm; Blood product unit; Ql; Ordinal; QL; Qualitative; Qual; Screen; Antibodies; Autoantibody; Antibody; Autoantibodies; Antby; Aby; Anti; BLOOD BANK I Ab SerPl BPU Ql Observation EMPTY_CLOB() 0.0 I Ab [Presence] in Serum or Plasma from Blood product unit
10847-2 Borrelia burgdorferi DNA ACnc Pt Body fld Ord Probe.amp LYME DISEASE MICRO QST 20061026 MAJ EMPTY_CLOB() EMPTY_CLOB() EMPTY_CLOB() 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() Deoxyribonucleic acid; B burgdor; B burg; B burgdorferi; Lymes; Burgdorf; Lyme; Bb; Lyme disease; Arbitrary concentration; Point in time; Random; BF; Fluid; Body fluid; FLU; Fl; Ql; Ordinal; QL; Qualitative; Qual; Screen; Amp Prb; Probe with ampification; DNA probe; Amplif; Amplification; Amplified; Microbiology B burgdor DNA Fld Ql Amp Prb Both EMPTY_CLOB() 0.0 Borrelia burgdorferi DNA [Presence] in Body fluid by Probe with amplification
1085-0 J little s super little b Ab ACnc Pt Ser/Plas^BPU Ord J (LITTLE S) (LITTLE B) AB BLDBK FS 20020722 MAJ EMPTY_CLOB() EMPTY_CLOB() EMPTY_CLOB() Js^b AB 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() ; Js sup(b); Js^b; J (LITTLE S) (LITTLE B); Arbitrary concentration; Point in time; Random; SerPl; SerPlas; SerP; Serum; SR; Plasma; Pl; Plsm; Blood product unit; Ql; Ordinal; QL; Qualitative; Qual; Screen; Antibodies; Autoantibody; Antibody; Autoantibodies; Antby; Aby; Anti; S prime; BLOOD BANK Js sup(b) Ab SerPl BPU Ql Observation EMPTY_CLOB() 0.0 Js^b Ab [Presence] in Serum or Plasma from Blood product unit
10852-2 Fungus identified Prid Pt Bld Nom Routine fungal culture MICRO QST 20030513 DEL EMPTY_CLOB() EMPTY_CLOB() DEL 601-5 EMPTY_CLOB() 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() Fung; Fungi; Identity or presence; Point in time; Random; Blood; WB; Whole blood; Nominal; Cult; Cultures; CandS; Isolated; Microbiology EMPTY_CLOB() 0.0
10855-5 Microscopic observation Prid Pt Duod fld/Gast fld Nom Ova and parasite preparation MICRO QST 20070508 MAJ EMPTY_CLOB() EMPTY_CLOB() EMPTY_CLOB() 1 EMPTY_CLOB() EMPTY_CLOB() Y false EMPTY_CLOB() Identity or presence; Point in time; Random; Duod or Gastric fl; Gastric contents; Gastric fluid; Duod fl; Duodenal fluid; Dudf; Nominal; OandP Prep; O+P; Ova and parasites; OandP Prep; Microbiology OandP Prep Duod or Gastric fl Both EMPTY_CLOB() 0.0 Microscopic observation [Identifier] in Duodenal fluid or Gastric fluid by Ova and Parasite Preparation
Now i have created the same structured table with no data
if i delete the rows from t1 it should go into t2
like wise i have written a trigger.
above mentioed ones are all records
|
|
|
Re: trigger [message #391511 is a reply to message #391507] |
Thu, 12 March 2009 07:29   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@raj
Immediately after you create the trigger can you type the following command?
The code should look something as follows:
SQL> CREATE OR REPLACE TRIGGER TR_Delete_TEST_TAB BEFORE DELETE ON test_tab
2 FOR EACH ROW
3 BEGIN
4 IF (:old.Col_2 <> 1) OR (:old.Col_2=1 AND :old.Col_1<>'D') THEN
5 INSERT INTO test_tab_new(Col_1, Col_2)
6 SELECT :old.Col_1, :old.col_2 FROM test_tab;
7 END IF;
8 END TR_Delete_TEST_TAB;
9 /
Trigger created.
SQL> show errors;
No errors.
SQL>
Regards,
Jo
|
|
|
|
|
Re: trigger [message #391520 is a reply to message #391383] |
Thu, 12 March 2009 07:39   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Should also point out that sql developer will show you any errors in the trigger.
But you should still run it in sqlplus so you can easily copy and paste the errors here.
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 15:43:06 CST 2025
|