Home » SQL & PL/SQL » SQL & PL/SQL » trigger (oracle 10g)
trigger [message #391383] Thu, 12 March 2009 00:52 Go to next message
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 #391393 is a reply to message #391383] Thu, 12 March 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is your problem in writing this? It is basic PL/SQL.

Regards
Michel
Re: trigger [message #391394 is a reply to message #391383] Thu, 12 March 2009 01:45 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
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 #391412 is a reply to message #391383] Thu, 12 March 2009 03:42 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

come on man...138 messages,senior member....FORMAT the code atleast once and send it...
Re: trigger [message #391416 is a reply to message #391412] Thu, 12 March 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
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 #391420 is a reply to message #391416] Thu, 12 March 2009 04:04 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i dont know the way to format that is the reason why this happend.
can you help me out in this scenario
Re: trigger [message #391421 is a reply to message #391416] Thu, 12 March 2009 04:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #391424 is a reply to message #391383] Thu, 12 March 2009 04:19 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

May be the attached screenshot helps u..

./fa/5888/0/
Re: trigger [message #391426 is a reply to message #391423] Thu, 12 March 2009 04:22 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
problem solved when i put begin before when condition
but trigger is not working
showing error as trigger is inavlid
Re: trigger [message #391428 is a reply to message #391426] Thu, 12 March 2009 04:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Query user_errors to see what is wrong.
Re: trigger [message #391431 is a reply to message #391411] Thu, 12 March 2009 04:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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 #391482 is a reply to message #391472] Thu, 12 March 2009 06:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Thank you
Re: trigger [message #391487 is a reply to message #391482] Thu, 12 March 2009 06:33 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
they are for old values for table 1 to table 2.
Re: trigger [message #391489 is a reply to message #391383] Thu, 12 March 2009 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
then tell oracle that
Re: trigger [message #391497 is a reply to message #391489] Thu, 12 March 2009 06:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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 #391504 is a reply to message #391500] Thu, 12 March 2009 07:05 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
after including old specifiers in if condition once again it is showing the same message like trigger is invalid

[Updated on: Thu, 12 March 2009 07:07]

Report message to a moderator

Re: trigger [message #391506 is a reply to message #391504] Thu, 12 March 2009 07:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@raj

Immediately after you create the trigger can you type the following command?
show errors;

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 #391513 is a reply to message #391511] Thu, 12 March 2009 07:32 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i am doing it in oracle sql developer is it possible with that command i dont think so
Re: trigger [message #391518 is a reply to message #391383] Thu, 12 March 2009 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Run the code to create the trigger in sqlplus
Re: trigger [message #391520 is a reply to message #391383] Thu, 12 March 2009 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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.
Re: trigger [message #391528 is a reply to message #391518] Thu, 12 March 2009 07:51 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ok its working thank ypu
Previous Topic: dbms_job issue
Next Topic: Creating one index for 2 columns or two
Goto Forum:
  


Current Time: Wed Dec 07 03:19:58 CST 2016

Total time taken to generate the page: 0.14842 seconds