Create Trigger So That Whenever Record In Employee Table Deleted?

Dec 7, 2009

I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.

CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);

[Code]....

View 11 Replies

Reinsert Deleted Record By Using Same Trigger

Jan 24, 2013

I have a trigger which monitors the deletion activity on the table. But I would like to re-insert those records to the in the table using the same trigger.

View 1 Replies View Related

Create Trigger To Stop Events From Being Deleted

Jun 2, 2010

I am trying to create a trigger to stop events from being deleted if the date is before the current date. This is the SQL syntax for my table

CREATE TABLE event
(event_id numeric(5) not null,
concert_id numeric(5) not null,
event_date date,
);

What I have attempted so far is:

CREATE OR REPLACE TRIGGER event_deleting
BEFORE DELETE ON event
FOR EACH ROW
BEGIN
IF DELETING THEN
[code]......

This gives me a few warnings, the first is that a DECLARE is missing and the second is a INTO is missing.

View 1 Replies View Related

Forms :: Firing Of When-create-record Trigger

Nov 5, 2012

I am navigating from a Master form to a child form. The when-create-trigger in the child form is sometimes executing and sometimes not. Not firing of this trigger is causing the child form to open without any initially assigned values. What is the root cause and also the scenarios where the when-create-trigger fires.

View 15 Replies View Related

SQL & PL/SQL :: Employee Table To Assign Grade For Each Employee Based On His Salary

Feb 9, 2012

based on the following information

grade lowsal highsal
------ ----- ------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

for the employee table to assign grade for each employee based on his salary the following plsql procedure is giving error:

-----------------------------------------------------------
CREATE OR REPLACE PROCEDURE GRADE(EID IN NUMBER,BONUS OUT NUMBER) IS

vGRADE NUMBER(8,2);
vSAL NUMBER(8,2);

BEGIN
vGRADE=1
SELECT SAL INTO vSAL FROM EMP WHERE EMPNO=EMPID;
IF vSAL<= 700 THEN
vGRADE:=1;
ELSEIF vSAL<= 1201 THEN
[code]....

View 4 Replies View Related

Forms :: ERROR - Record Must Be Entered Or Deleted First

Jul 19, 2011

I'm getting below error. how can we avoid this error?

ERROR: frm-40102 record must be entered or deleted first

View 4 Replies View Related

Moving A Record To Another Table In A Trigger

Mar 12, 2012

I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..

CREATE OR REPLACE PACKAGE BODY trigger_api AS
PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;
[code]....

Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....

View 1 Replies View Related

Moving Record To Another Table In Trigger?

Mar 9, 2012

I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.

It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.

Doing this process from code is not an option and MUST happen automatically via triggers.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Insert Record Into Another Table

Aug 17, 2010

writing a trigger body. My requirement is i need to insert a new record in a task table when ever a new record is inserted into employee table.Here in the trigger i need to select the name of the employee in the last inserted row in employee table and insert the name in task table.I tried to write the code as below

insert into task(name, date, type) values ((select name from employee where emp_id=(select max(emp_id) from employee), sysdate, 'document'));

When i am trying to insert record using trigger, it is taking last but one record from the employee table.

View 2 Replies View Related

SQL & PL/SQL :: Moving Record To Another Table In Trigger?

Mar 9, 2012

I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table. It must happen in triggers, but I keep getting the mutating error. I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.

Doing this process from code is not an option and MUST happen automatically via triggers.

View 3 Replies View Related

SQL & PL/SQL :: Create Table Using Trigger

Feb 1, 2013

Is it possible to create table using trigger?

I tried but failed...

CREATE OR REPLACE TRIGGER tri1
AFTER UPDATE
ON dept
FOR EACH ROW
declare
pragma AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate 'create table dept_dum as select * from dept';
END;
/

SQL> update dept set deptno=23 where deptno=10;
update dept set deptno=23 where deptno=10
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI1'

I am running this as scott user

My requirement is to

create table as select * from table_name where flag=1

This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.

View 4 Replies View Related

SQL & PL/SQL :: Create Trigger On Table After Update?

Apr 16, 2013

i have created a trigger on a table after update. i am using if condition if the condition is true i am passing a value and then inserting into audit table.

if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;

Then i am inserting

Insert into audit_table(change_type.....)
values(..L_change_type)...)

If i want to skip the insert statement for particular condition wt i have to do.

View 7 Replies View Related

SQL & PL/SQL :: Create Test Table - Instead Of Trigger

Dec 30, 2010

table
CREATE TABLE "TEST"
("CONTRACT_REF_NO" VARCHAR2(20 BYTE) primary key,
"CHG_GL" VARCHAR2(9 BYTE),
"CHG_GL_1" VARCHAR2(9 BYTE),
"CHG_GL_2" VARCHAR2(9 BYTE),

[code]...

10 rows selected.

create or replace view test_view
as
select 1 id,CONTRACT_REF_NO,chg_gl
from test
union all

[code]...

11 rows selected.

I have done only for one id.

so the problem is the front end is showing this test_view that people will modify.but they have to actually modify the table ,so I can think of an instead of trigger(instead of insert and instead of update ones).

View 16 Replies View Related

SQL & PL/SQL :: Create Trigger On (account) Table?

Nov 23, 2008

I gave this a shot on my own, and failed, so I am putting the question to you:

I am looking to create a trigger on the "Account" table

CREATE TABLE Account(Acct_no number, BillAmt number, OpenDate Date, SSN number,
Primary Key (Acct_no),
Foreign Key(SSN) references ResponsibleParty(SSN));

which will update the BillAmt field whenever one of the account's respective records is inserted/updated/deleted from the following tables:

CREATE TABLE Plan(Plan_id number, Cost number, Minutes number,
primary key(Plan_id));
CREATE TABLE Feature(Feature_id number, Description varchar(20), Cost number,
primary key(Feature_id));

These tables are linked through the following table:

CREATE TABLE Phone_Line(MTN number, cStart Date, cEnd Date,
rDate Date, Status char, ESN number, Acct_no number, Plan_id number,
primary key(MTN),
foreign key(ESN) references Equiptment(ESN), foreign key( Acct_no) references Account(Acct_no),
foreign key(Plan_id) references Plan(Plan_id));

View 26 Replies View Related

Forms :: Create A Report For Absent Employee?

Oct 18, 2012

I have a requirement that I need to create a report for absent employee, we have "Hand Punch" scanning system for attendance, when a employee scan his hand it put a record with the employee id, date, shift, & time.

Now I need to create a report for absent employees in date range

View 2 Replies View Related

SQL & PL/SQL :: Create Trigger On Certain Column For Table Structure

Nov 3, 2011

create trigger on certain column for table structure.

SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....

I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .

Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)

Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column

View 3 Replies View Related

Create A Trigger Before Update On Table Test1?

Dec 25, 2010

i have two table:

QUOTE create table test1(
num number(4),
name varchar2(50));
create table test2(
num1 number(4),
name1 varchar2(50));

then i want to create a trigger before update on table test1 that i want to insert in table test2 the old values in table test1:

for example

QUOTE insert into test1(1,'cocol');
QUOTE update test1
set name='oracle' where num=1;

so i want the trigger to insert 1 and cocol into the table test2.

View 1 Replies View Related

PL/SQL :: Action Of Trigger To Create Records In Table

May 14, 2013

Using oracle 11.2.0.3 and wish to do the following.

Have trigger on table a on database a.

When a record is inserted into table a a record should be written first to table1 on database b , committed then a record written to table2 on database b as table2 has fk dependency on table 1.

How can we best achieve this.

Getting problems with the commit inside the trigger have tried pragma autonous transaction as well.

Get ora-02064 distributed transaction not supported when use the following code

create or replace trigger por_TRG_POP_IKNOW_MULTIPLE2

after INSERT OR UPDATE or delete   on por_MULTIPLE

for each row

-- tO ENSURE I-KNOW KEPT UP-TO-DATE WITH HOMIS MULTIPLE
-- delib not delet form i-know as if has been used by customer cannot delete
-- due to referential inetgrity constraints in i-know
-- Note parent_num updated on IKW via support.maintain_latest_dim_version.update_multiple until
-- When IKW becomes master of multiple info, this trigger can be removed
-- and support.maintain_latest_dim_version.update_multiple removed

declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_dml_type varchar2(1);
begin
IF INSERTING OR UPDATING

[Code]....

--when matched THEN update set
--      a.MGRP_NUM b.mult_link_multiple_num,
--      MGRP_DESCRIPTION = (select mult_name from por_multiple
--     where mult_code = mult_link_multiple_code

when not matched

then insert
(
a.MGRP_NUM,
a.MGRP_DESCRIPTION

)

[Code]...

View 7 Replies View Related

WebService - How To Create Record Type And PL/SQL Table

Oct 7, 2010

I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.

here are the output values:

Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)

create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger To Change Every Insert In Table To Sysdate

Feb 17, 2011

I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'

Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL

View 4 Replies View Related

SQL & PL/SQL :: Create External Table Based On Record Identifier?

Oct 21, 2011

I have a csv file which is in this format -

qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0

Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?

For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.

I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.

View 11 Replies View Related

Create Trigger On Table / Populate With Data From Additional Tables

Nov 7, 2010

I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.

e.g.

NAME_TABLE
-reg_id
-name

ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id

PROCESS_TABLE
-srv_id
-start_time
-end_time

This is what I would like the logging table to look like:

LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time

How can I go about creating this type of trigger?

View 2 Replies View Related

SQL & PL/SQL :: Create Test Table - Trigger Throw Error Message

Mar 4, 2010

create table test123 as (unit varchar2(5),qty varchar2(25));
insert into test123('ABC','10,40,50');
insert into test123('PQR','20,30,40,10');
insert into test123('XYZ','20,10,70');

I have a table called test123 which qty field. if the sum of qty is entered more than 100 or less than 100, it should throw error.

I wrote this trigger..but it is not working.

create or replace restrict_sum
after insert or update of qty on test123
for each row
declare
v_sum number;
[code].........

View 5 Replies View Related

SQL & PL/SQL :: Print Ename And Salary Of Employee Using Employee Number As Input

Aug 11, 2013

/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......

Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.

View 25 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

SQL & PL/SQL :: Deleted All Records From Table - How To Get Back

Nov 8, 2010

I have deleted all the records from the table.And I have committed.Now I want to get all the records back.

View 16 Replies View Related

SQL & PL/SQL :: Deleted Table In Oracle 11g - How To Restore

Mar 11, 2013

I deleted a table in oracle sql 11g about two weeks ago and commited. Didn't realise that I would need it later on and now I needed. is there a way a can get reverse this.

View 13 Replies View Related

Possible To Recover A Deleted Column Of A Table

Mar 18, 2011

I am new to oracle database. I wanted to know if it is possible to recover a deleted column of a table. If yes, how can it be done? I tried flashback but it seems that flashback doesn't support recovery of deleted column.

View 2 Replies View Related

Restore Deleted Records From A Table

Jul 31, 2012

Somebody deleted records from 10,12 tables in one of the schema. I found in one of the forum that table can be restored also if it exists in the recycle bin. I checked by querying:

select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

> select type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin WHERE TYPE='TABLE' and droptime like '%2012-07-31%';
PHONE      TABLE                 YES YES 2012-07-31:10:23:08
TABLE                 YES YES 2012-07-31:10:23:08
TABLE                 YES YES 2012-07-31:10:23:08
TABLE                 YES YES 2012-07-31:10:23:08
TABLE                 YES YES 2012-07-31:10:23:08
- -
- - I created a test table and dropped it and restored from recycle bin using following flashback table test query....but in my case tables are not dropped...these tables are shoing 0 records. How can I recover only records from recycle bin tables?
SQL> flashback table test to before drop;

Flashback complete.

DB=10.2.0.5 on RHL

View 6 Replies View Related