SQL & PL/SQL :: Trigger - Send Booking ID To Audit Table

Jun 3, 2010

I'm trying to create a trigger that will come into play after the user has inserted a 0 for the booking evaluation, i need the booking id sent to the audit table. This is what I have tried so far:

CREATE OR REPLACE TRIGGER zero_evaluation
AFTER INSERT OR UPDATE ON booking
FOR EACH ROW
WHEN (NEW.Evaluation=0)
BEGIN
INSERT INTO audit (audit_id, booking_id, Reason)
VALUES (audit_id_seq.NEXTVAL,:NEW.booking_id, 'Contact customer for 0 evaluation');
END;

I get the following:
Line 2 PL/SQL: SQL Statement ignored
Line 3 PL/SQL: ORA-02289: sequence does not exist

This is my SQL statement:
CREATE TABLE audit
(audit_id numeric(5) not null,
booking_id numeric(5) not null,

[code]...

View 5 Replies


ADVERTISEMENT

PL/SQL :: Client Identifier In Web App For Use In Audit Trigger?

Feb 1, 2013

in my java web app running on Weblogic which uses connection pooling, I am calling an Oracle Stored Proc that sets client identifier to userid using dbms_session.set_identifier

I then retrieve it in triggers attached to db tables to capture before-after image

I must have missed a step because this is what I find (testing locally):

1. I login to my app as USER1 and update some data

2. I check the audit tables and sure enough last_updated_by is set to USER1

3. Without logging out, I start a different browser (Firefox, in step 1 I used Chrome) and login as USER2

4. I now switch to USER1 session in Chrome browser (still logged in as USER1) and update some data

5. This time I see that the audit tables show last_updated_by as USER2 (instead of USER1)

View 11 Replies View Related

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

Jun 13, 2010

I want to make a trigger , to store values that are affected during any DML(insert,update,delete).

CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Creating A Flight Booking System

Dec 1, 2010

i am trying to create a flight booking system,i am at the stage of creating the tables but am having trouble with creating one of the tables

CREATE TABLE Seat
(Seat_no NUMBER(3) CONSTRAINT PK_Seat PRIMARY KEY,
Class_Code CHAR(2) CONSTRAINT FK_Class_Code FOREIGN KEY (Class_Code) REFERENCES Class(Class_Code)
CONSTRAINTS CHK_Class_Code CHECK(Class_Code='EN' OR Class_Code='FS')
(Class_Code is a primary key in the table Class)

It comes up with an error saying ORA-02253: Constraint specification not allowed here and if i put a coma after the first constraint it says the name already used for an existing constraint.

View 18 Replies View Related

SQL & PL/SQL :: Audit Particular Table

Apr 20, 2012

We can audit a particular table alone, I would like to audit one table, to find all different kinds of queries fired (Including select, insert and update) over a period of 2 months.

View 4 Replies View Related

Updates In Audit Table

May 22, 2011

I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.

1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.

I am unable to figure out the proper way to implement the point 2 above in oracle database.

View 3 Replies View Related

PL/SQL :: Audit Change In A Table?

Feb 27, 2013

We need to audit what data has changed from Vendor_B table when compared to Vendor_A table.

There is two tables:

a) VENDOR_A
b) VENDOR_B

VENDOR_A and VENDOR_B table does not share the same structure but have 1 column in common - the ID and may or may not having changed Coordinates.

We need to audit what coordinates has changed from Vendor_B data when compared to Vendor A and get the report back in the format of:

<pre>
               VENDOR_A VENDOR_B                    
ID     Change_date     LONGITUDE     LATITUDE     LONGITUDE     LATITUDE     
873     1/02/2013     -33.46711315     151.3195056     -33.46211316     151.3245057          
694     3/02/2013     -33.46721315     151.3198056     -33.46214312     151.3265053     

</pre>
---------------------------------------------------------------------------------------------------------------------------------------------------
--Found a sample query from Ask Tom but I am not sure how I can apply them to get the structure above.
---------------------------------------------------------------------------------------------------------------------------------------------------
<pre>
create table base_tab (pk number, column1 number, column2 varchar2(30), column3 date);
Create table refresh_tab (pk number, column1 number, column2 varchar2(30), column3 date);
Create table diff (pk number, column1 number, column2 varchar2(30), column3 date, base_tab number,
refresh_tab number, action varchar2(30));

[Code] .....

--clean out the differences table
truncate table diff;

--------------------------------
-- build the difference data
------------------------------------
insert into diff select pk,column1,column2, column3,
count(src1) base_tab, count(src2) refresh_tab, null action
from
( select a.*,

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Audit Table With Primary Keys?

Feb 19, 2013

I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.

DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);
CREATE TABLE audit_table

[Code].....

View 10 Replies View Related

Audit Dml On Few Table For Non Application Users

Oct 12, 2013

I want to audit  dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it.

View 4 Replies View Related

Forms :: Commit After Insert Into Audit Table

Jul 5, 2013

I am just making a audit table as well. i have learnt the basics from here URL.....

My problem is that after inserting into audit table if i issue commit command then the table + unsaved data present on the form is also saved.What i want is that i issue a command which save only inserted record in audit table, and should NOT save data present on the data entry form. (which will be saved later by other method/button).

View 9 Replies View Related

Performance Tuning :: Audit Analyze Table On 9i Database?

Apr 19, 2011

I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.

Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.

Is there a way to globally turn of ANALYZE TABLE in a 9i database?

View 2 Replies View Related

Audit Vault/Firewall :: How Max Storage Can Audit Vault Server Support

Aug 18, 2013

AVDF current version 12.1 not support External/SAN storage. my question is, if customer get a huge number of Audit log and DBFW event records, then how max size can Audi Vault server support for online data (not archive data)? and can I use a Hardware server with multiple HDDs for AV Server?

View 0 Replies View Related

Forms :: Record To Be Inserted Into Table As Audit-trail Of Printing

Feb 3, 2010

I am using oracle developer 10g. I want to know the status of the printer where i want to print. If the running report is printing or in queue then a record is to be inserted into a table as audit-trail of printing. Idon't want to do it manually.

View 1 Replies View Related

SQL & PL/SQL :: Send Records From One Table To Another

Apr 21, 2011

I have requirement like we need to send records from one table to another table. for example if i have 4 records in Table A , first i need to send only 2 records to Table B then again rest 2 records to the Table B.

View 10 Replies View Related

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

Security :: Audit Specific Statement On Specific Table By Specific User?

May 29, 2012

I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :

AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;

I get an error, where is my syntax error ?

i want to know if it's possible to do it without trigger ?

View 7 Replies View Related

Using Trigger On One Table To Update Another Table

Aug 28, 2011

My problem is the following:

At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.

Tables:

purchase orders table P_ORDERS:
P_order_number,
P_poz_number,
S_order_number,
S_poz_number,
P_delivery_date

sales orders table S_ORDERS:
S_order_number,
S_poz_number,
S_delivery_date

My question is:

Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?

View 2 Replies View Related

SQL & PL/SQL :: Trigger Updating Second Table With PK Value

Oct 20, 2011

Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?

My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...

View 5 Replies View Related

SQL & PL/SQL :: How To Update Another Table Using Trigger

Nov 24, 2011

CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig
BEFORE delete or insert or update on EMP
FOR EACH ROW
BEGIN
if UPDATING then

[Code] .....

View 2 Replies View Related

SQL & PL/SQL :: Trigger - Does Not Update Table?

Nov 24, 2010

I am ceating a trigger to...when I update or insert a record in table and if salary of that record is less than 1600 than it should be updated to 2000. My trigger is

CREATE or REPLACE TRIGGER myTrigger
BEFORE UPDATE OR INSERT ON newemp
FOR EACH ROW
BEGIN

[code]...

There are no errors with trigger while compiling, but when I update or insert a record, it does not update the table. It looks like the trigger is being ignored.

View 2 Replies View Related

SQL & PL/SQL :: Trigger All DML At All Table Events?

May 4, 2011

i want to write, just ONE trigger that handles all DMS events at schema level and writes the field changes to a specific log table. i wanna to write like 'FOR EACH FIELD' as for each row (& also each table). it turns in the fields section and if the 'old' and 'new' values isnt same, it triggers this function & writes changes to log.

ex. is like:

FOR EACH ROW
FOR EACH FIELD
if (old.value != new.value)
insert into LOG(..., old.name,new.name) values (..., old.value, new.value)
NEXT // FIELD
NEXT // ROW

View 19 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 :: DDL Trigger After Alter Table

Jul 22, 2010

I created a DDL trigger which manipulates columns of a table after an "alter table" statement.

For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".

Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".

My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.

The connection will be destroyed after you tried to see the data of this table.

Also the export (creating a dump) will not work.

The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".

How I can get the information what kind of alter table statement will be executed?

Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?

create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...

-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';

--For example:

-- alter table XXX modify NEW_COL varchar2(20)

execute immediate vSTATEMENT;

exception
...
end;
/

View 11 Replies View Related

Creating A Trigger To Populate Another Table

Jan 4, 2007

Having trouble creating a trigger to populate another table.

The SQL:

CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;

/
ALTER TRIGGER "P_M_YES" ENABLE
/

I get an invalid trigger specification.

View 3 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

Modify One Table To Trigger Other Tables

Aug 20, 2012

We are running an Oracle 10g server.

We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.

I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.

I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.

View 13 Replies View Related

Oracle Trigger - Update Same Table?

Mar 30, 2010

I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.

So far I have:

DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;

[Code]....

I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.

The procedure IsCDCUser just checks it should be updating for this user.

The problem I get is the following error:

Bind Variable "NEW" is NOT DECLARED

View 3 Replies View Related

SQL & PL/SQL :: Write Trigger On Table Which Contain Lot Of Parameters?

Apr 17, 2012

I have to write a trigger on a table which contain lot of parameters.But i need to pick a specific row and check that without disturbing other stuffs.Is there a way to write Before update trigger on a particular rows filtering the unneccasary rows.

The requirement is when user update the date from front end (Java application) the trigger should check the date and validate that it should be month end date. For example.

1)04/21/2012 wrong date
2)04/30/2012 correct date
3)03/29/2012 Wrong date
4)03/31/2012 correct date

View 7 Replies View Related

SQL & PL/SQL :: Trigger To Copy Data From One To Another Table

Jun 28, 2010

1. I am inserting a value to a table.through uploading the csv file to the DB table using apex.

2. After the data get inserted into the table(parent),my data should be copied to the another table with some conditions .

3. Is it possible to write a trigger to insert statement from selecting the values from another table.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved