Creating Trigger For Views

Sep 21, 2008

I have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"

So I created a view such as

Quote: create or replace TRIGGER trig_order
BEFORE INSERT OR UPDATE ON vworder
FOR EACH ROW
DECLARE
cust_id VARCHAR2(20);
BEGIN
SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20000, 'Customer ID Does Not Exist');
END;

But it comes with error

Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.

I am not an expert at all and I need to finish this today itself.

View 7 Replies


ADVERTISEMENT

Creating Self-updating Views With Data Selection?

Aug 4, 2011

I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.

I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).

View 1 Replies View Related

SQL & PL/SQL :: Creating Views - Percentages And Multiple Tables

Oct 10, 2013

I'm having trouble with a little assignment.

"Create a view named vuPassFailRate that will show the pass rate and fail rates of each test."

I have a table named Test_ID containing the following columns:

TEST_ID
TEST_NAME
PASSING_GRADE

And another table named Test_History containing the following columns:

TEST_ID
STUDENT_ID
SCORE

I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables.

I have been staring at this problem for so long now i cant see the wood for the trees.

View 3 Replies View Related

PL/SQL :: Creating Views - Percentages And Multiple Tables

Oct 10, 2013

I'm having trouble with a little assignment. "Create a view named vuPassFailRate that will show the pass rate and fail rates of each test." I have a table named Test_ID containing the following columns:

TEST_IDTEST_NAMEPASSING_GRADE 

And another table named Test_History containing the following columns:

 TEST_IDSTUDENT_IDSCORE 

I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables. I have been staring at this problem for so long now i cant see the wood for the trees. 

View 2 Replies View Related

Performance Tuning :: Nested Select / Instead Of Trigger And Views - No Index Used?

Sep 8, 2009

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:

1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.

My problem is this:

If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.

Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.

SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete

[code]...

OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?

SQL> DELETE FROM PLAN_TABLE;

5 rows deleted.

SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV

[code]...

View 10 Replies View Related

SQL & PL/SQL :: Creating A Trigger

Jul 19, 2011

I need to create a trigger for the below case:

index_id Time_vertex_id date rate pre_rate
4 1 17-06-2011 4.7 6.4
4 1 16-06-2011 6.4 7.4
4 1 14-06-2011 7.4
4 1 15-06-2011 8.4 7.4

the index_id and time_vertex_id will be unique and when the date is 17th i.e the first date will be inserted the current rate will be 4.7 and the previous rate will be blank and when another date is inserted i.e 16th the previous rate of 17th will be the current rate of 16th i.e 6.4.

when 14th is being inserted, the previous rate of 16th will be the current rate of 14th i.e 7.4 and if after 14th is being inserted, 15th is being inserted, then the previous rate of 16th should be updated as per the current rate of 15 say 8.5.

View 5 Replies View Related

Creating A Trigger Within If-else Statement

May 25, 2012

I would like to create a trigger on a table, but only if the table exists.

This is what i've got so far:

create or replace function tableExists (tablename in varchar2) return boolean
is
c int; 
begin    

[Code].....

This would give me the error:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update

View 1 Replies View Related

Creating Trigger Through Procedure

Jul 24, 2009

I am trying to create trigger through Procedure due to following reasons-

1. The name of the column on which trigger will execute, is to be fetched dynamically.
2. The name of the column to be updated, is to be fetched dynamically.

Here is the sample code-

CREATE OR REPLACE PROCEDURE test2
IS
var VARCHAR2 (4000);
uname VARCHAR2 (30);
attribtask VARCHAR2 (100);
mapcol VARCHAR2 (100);
BEGIN
[code].........

On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.

In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.

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

Creating A Trigger To Compare Dates

Mar 22, 2007

I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.

There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.

Here is what I have so far.

CREATE OR REPLACE TRIGGER clean_tableName
AFTER INSERT OR UPDATE
ON tableName

BEGIN

END clean_tableName;

As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?

I'm thinking something like:

DECLARE
laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date.
BEGIN
DELETE FROM tableName WHERE DATE_UPDATED > laterDate;
END clean_tableName;

But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?

View 2 Replies View Related

SQL & PL/SQL :: Creating Trigger To Compute Population From Groups

Aug 14, 2011

I am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.

CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),

[code]...

View 11 Replies View Related

SQL & PL/SQL :: Creating Trigger - Table Or View Does Not Exist

Apr 6, 2012

When i try to create a trigger , i ended up with error.

SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,

[code]....

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist

The command used to resolve the error is

GRANT SELECT ON v_$session TO jack;

Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?

View 3 Replies View Related

SQL & PL/SQL :: Creating Trigger - Check If Data Being Inserted Already In Table And If Not Insert It

Aug 24, 2011

I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;

what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Trigger (Alter Command) And Creating Meta Data Table

Sep 20, 2011

i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)

CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/

Meta table contains Table name and column name..i attached the table data in atext file

View 39 Replies View Related

Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?

Nov 5, 2012

is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?

in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.

View 2 Replies View Related

Refresh Materialized Views Based On Remote Views?

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table

Oct 17, 2013

I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by  querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.

View 3 Replies View Related

Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views

Jul 7, 2010

I have to convert some existing materialized views (fast refresh) to partition materialized views.

Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.

1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option

But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.

Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?

View 3 Replies View Related

Forms :: When-Timer-Expired / Creating A Timer On On-Error Trigger Of Block

Jan 25, 2012

I'm having a problem with the When-timer-Expired trigger.I am creating a timer on the On-Error trigger of the block. in the when-timer-expired :

message (11); exit_form(no_validate);

If i run the program program for the first time,message 11 doesn't show thus it doesn't fire the when-timer-expired trigger. But if i logout from application server and enter all over again,the when-timer-expired works !

View 8 Replies View Related

Forms :: Call One Trigger Of Item In Trigger Of Form?

Jul 1, 2011

How can "call one trigger of item in trigger of form"

View 5 Replies View Related

Why To Need Materialized Views

Aug 26, 2010

Why do we need materialized views when the normal views also serve the same purpose?

View 2 Replies View Related

Hit All Views One After Other In Database?

Aug 26, 2003

I am writing a checker that should hit all the views one after other in database and report if there is an SQL error ( Page: Data cannot be retrived from the view XYZ).

The query I am running is
"select * from $view fetch first 1 rows only".

better query which runs faster as I have to query all the views in db in the script ( script runs every 10 mins on cron).

View 5 Replies View Related

Materialized Views Not Getting Refreshed?

Dec 29, 2010

I have a problem Mat view refresh...I have to take a snapshot of the table before nsert or update to the table....below is the code written to achieve this..

create materialized view my_view refresh complete as select * from t2;
create or replace trigger TRG_T2
before insert or update on T2
declare
X number;

[code]....

However this is not getting refreshed... Also i noticed that it works for the very first time (after first insert or update) and then for successive ones it doesnt.. ..

View 1 Replies View Related

How To Refresh Materialized Views

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

how the refersh happens or how do I specify the refresh clause.

REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

Select All Views From Schema

Apr 27, 2010

I want to list out all the views in a schema. What table can I query?

View 2 Replies View Related

SQL & PL/SQL :: Usage Of Hints In The Views

Oct 11, 2011

I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows

create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);

my question is how to use hints on this table if your are using view to access the data from internal table.

Normal structure is if i don't wrong:-

index( <<view name|view alia name>> <<table name|table alias name>> name of index)

Consider my case

select * from view_t130 where <index_column> --not picking up index

i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.

select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>

how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.

View 1 Replies View Related

SQL & PL/SQL :: Refresh Views And Function?

Aug 17, 2010

Is there any function to refresh all views and functions in oracle.

View 4 Replies View Related

SQL & PL/SQL :: Refresh All Materialized Views?

Nov 28, 2011

I have n number of materialized views in my schema ..I want to refresh all M_views at once by using cursor

View 3 Replies View Related

SQL & PL/SQL :: How To Create Dynamic Views

Jun 26, 2010

How to create dynamic views? I need literature on dynamic views.

View 5 Replies View Related

SQL & PL/SQL :: Bind Variable In Views?

Dec 27, 2010

how to avoid the bind variable in view.

The query is correct but it contains bind variable Based on this query, View has to be created for report

like example select * from emp where deptno = :deptno

How to get the correct result by avoiding bind variable because view does not accept bind variable.

View 2 Replies View Related







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