SQL & PL/SQL :: Advantage And Disadvantage When Use Trigger On Database?
Aug 14, 2011now i will built new project and want know what is the advantage and disadvantage when used the trigger on database
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: What Is The Advantage Of Nested Table
May 17, 2011what is the advantage of Nested table ?
View 1 Replies View RelatedBackup & Recovery :: Advantage Of Using RMAN In Oracle 11g Standard Edition
Apr 15, 2013We are upgrading from oracle 9.2.0.8 to 11g r2, and both are Standard Edition database. The database is part of a product that runs on a customer site, and won't get bigger than 50 GB. It runs in archive mode, and our backup script does a hot backup every night, plus copying the archive logs, redos, controlfiles, etc. We save 2 entire backups - from the last night plus the night before last. Then there's a tape backup that saves the backed-up files to an off-server location.
This architecture has allowed us to recover our customer's data from many odd occurrences at customer sites (power loss during a hot backup, corrupt controlfiles/datafiles/archivelogs). My question is, given that we are running the Standard Edition database, which doesn't have most of the useful RMAN features, is it worth it to switch to RMAN?
I took an Oracle Backup and Recovery class and posed this question to the instructor, and the response was, it would be better to use RMAN over a manual user backup script. Our backup script is pretty battle-hardened - is that the best reason?
SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function
Jun 10, 2010What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
Why Database Trigger Can Became DISABLED
Apr 30, 2013We have an application, using ddl database trigger on oracle database.Trigger depends on package and several tables, package depends on user defined function.It works on different customer sites well, but for one customer (11g?), sometimes (cannot determine when and why), trigger became disabled.
I know, that trigger can became invalid, when its dependencies changed (i think in this situation oralce probably try to re-compile it). I understand that it can changed to be invalidwhen dependencies are not valid.I know I can alter trigger to became disabled manually.
But when it can became disabledwithout explicit alter statement be run!? Are there some conditions / circumstance when Oracle changed the trigger to be disabled?We'll ask the customer DBA team to enable audit on trigger, but it will take time to be done..
Database Trigger To Subtract 1 Day
Mar 14, 2012I am trying to write a trigger to reduce 1 day from 4 date fields of a table (order)
table name = order
fields = date_1, date_2, date_11, date_12 and dest_id
for example: the trigger will verify if orders that are dropping is for a specific customer (dest_id). if yes, the trigger will subtract one day from these date fields (by updating them); if no, (the dest_id is not for this customer) the trigger will not update anything in the DB.
SQL & PL/SQL :: When Database Trigger Created
Jun 28, 2010if i want to know when (date/time) that the Database Trigger created? I am using Oracle 8i, so is it possible to check when (date/time) ?
View 2 Replies View RelatedSQL & PL/SQL :: Database Trigger Is Not Firing?
Jun 9, 2010I have a database trigger that fire on delete or update. It works fine. But after few days, only delete is working. When I update, nothing is happening. Then I re-create the trigger and it works fine. Then the problem comes back after few days.
The code is for both action so I wonder why this happens:
AFTER
DELETE OR UPDATE
ON AGENCY.CCS_TEMPLATE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
I checked the trigger on all_objects table and it is valid. On schema browser it is compiled.
SQL & PL/SQL :: Create A Trigger With After Logon Database?
Apr 28, 2011i would like to trace some connexions on database, once compile the code , i have the issus following:
10/2 PL/SQL: SQL Statement ignored
11/20 PL/SQL: ORA-00942: table or view does not exist
this is the
DROP TABLE connect_user;
CREATE TABLE connect_user
(
nom_ora varchar2(15),
naom_os varchar2(15),
programme varchar2(20),
poste varchar2(20),
[code]...
Validation In Database Through Statement Level Trigger
Mar 23, 2011I have a table which contains data of all calcualted payements for a Land for a particular period i.e. for a Land 124 for the duration of Jan-2010 to Mar-2010 100 rupees rent is paid to the owner.Requirement here is that for a particular duration i.e is for Jan-2010 to Mar-2010 system calculation of rent for a land file multiple times where the table would contain history of each transaction i.e. a user can calculate for a Land 124 for duration fo Jan-2010 to Mar-2010 rent more than ten times and there would be ten records in the table but only one record out of these would be valid where the user cannot calcualte rent for Lnd 124 for Jan-2010 to Mar-2010 without updating the status of the last record in the table for the same land and duration to invlaid. I want to add this business validation through triggers as i am not able to do so through check constarinst.I want to ensure that only one record should be valid for a particular land for a particular duration since row level triggers do not allow us to query the table ion which the trigger is executing i am trying to use statement level trigger but i have a few doubts here: Incase of a Before Insert statement level trigger does the trigger have access to the data which is being inserted into that the table cause whenever a record is being inserted into a table the systen would check through the trigger that if there is a valid (payment_status is valid) payment record in the table for that same land file and same duration.If there is a valid record then the trigger would through an exception. I know you can use a row level trigger with an autonomous transaction should work wihtout giving the mutating table error issue but i wont to know if using a statement level trigger here is feasible!!
View 11 Replies View RelatedSQL & PL/SQL :: Create Trigger Which Will Test Database Link?
Aug 29, 2012I want to create a database trigger which will test the database link, if it is ok then it will use dblink and do its work.
If it fails then it will send the data into its own server logfile.
I Wrote:
CREATE OR REPLACE TRIGGER PERMIT.TESTTRG
AFTER INSERT OR UPDATE
ON PERMIT.TR_LP_M_H_COMPANY_25072012
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
Declare
l number; nIgn PLS_INTEGER; nRows PLS_INTEGER := 0;
[code]....
When I execute it it is giving error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PLS-00201: identifier 'EXEC_SQL.CONNTYPE' must be declared
4/11 PL/SQL: Item ignored
8/2 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
[code]....
Server Administration :: Preventing Database Shutdown By A Trigger?
Jan 15, 2013i mean can i write a trigger to prevent shutdown in certain time.
create or replace trigger trig1 before shutdown on database begin if to_char(sysdate,'hh24') <16 then 'what is the right thing to do here?'
SQL & PL/SQL :: How To Call Stored Procedures Or Functions From A Database Trigger
Oct 14, 2011How to call stored procedures or functions from a database trigger?
View 1 Replies View RelatedSQL & PL/SQL :: Which Privileges Required To Create A Trigger Like After Logon On Database
Mar 17, 2011Which Privileges required to create a trigger like after logon on database. I have grant Create any trigger privilege to user but still not able to create and give me an error insufficient Privilege.
View 5 Replies View RelatedSecurity :: Fire Trigger If And Database Object Will Created?
Nov 7, 2013How to write and fire trigger if any database object will created?
View 1 Replies View RelatedSQL & PL/SQL :: Created Trigger On Database Level In System Schema
Jun 10, 2010I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.
CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;
Forms :: Call One Trigger Of Item In Trigger Of Form?
Jul 1, 2011How can "call one trigger of item in trigger of form"
View 5 Replies View RelatedPL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?
Apr 16, 2013I Created One Trigger as Follows
CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
SQL & PL/SQL :: How To Call Trigger In Another Trigger
Mar 10, 2010Can we call trigger in another trigger,if it is possible any example.
View 1 Replies View RelatedSQL & PL/SQL :: Getting Sum Of Value In Trigger?
Sep 9, 2010CREATE TABLE TEST
(
EMPNO NUMBER,
SAL NUMBER
)
/
CREATE TABLE SUMSAL
(
[code].....
Say I have emp table. Now what I want is that whenever I enter new records in EMP table, I want to sum up the value inserted for SAL column and I want to do some task based on that value.
I'm inserting the EMPNO and SAL in test table whenever a row is inserted in EMP table. Now from there I want the SUM(SAL) column. As you can see I took the sum and inserted it into another table (or I can update a packaged variable with this value). It is inserting as many rows in SUMSAL table as many I insert in emp table through Oracle Forms. I want it to insert only one row in SUMSAL table because after user enters all the data for EMP table, I want to capture the sum of Salary inserted and then I want to perform some tasks based on that value.
SQL> select * from test
2 /
EMPNO SAL
---------- ----------
1111 100
2222 200
SQL> select * from sumsal
[code].....
-- Here it should be only one value. i.e: Sum(SAL) column. That is 300. But whatever task I want to perform it performs it for each row inserted. But I want to perform it after trigger inserts all the rows in TEST table.
I wrote an after insert trigger at table level but still I'm getting the same result.
SQL & PL/SQL :: Instead Of Trigger
Apr 29, 2011We have created instead of triggers on view.When ever we drop the view and recreate .It drop the triggers as well.Is there a way to preserver the trigger.. so that we can drop the view and trigger will not get dropped.
View 5 Replies View RelatedExecute Immediate In Trigger
Apr 17, 2013I am trying to use execute immediate in a trigger. See the below example
create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;
create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......
insert into test_tbl values(3,'test',sysdate);
I got the Error : -1008->ORA-01008: not all variables bound
In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.
Which Trigger Will Be Fired First
Sep 4, 2013There is a Table, on that table there are 2 triggers are written 1) row level trigger 2) statement level trigger. Now i am inserting one row into that table, so which trigger will be fired first and why?
View 3 Replies View RelatedUsing Sequence In A Trigger
Jun 1, 2009I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:
App_Desc Request_ID
-----------------------
DEV 100 1
DEV 101 2
STG 100 3
STG 101 4
Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.
Using Variables In A Trigger?
Oct 24, 2007consider the trigger below,
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
Trigger Corresponding To A Sequence
Apr 16, 2007I have 3 tables whose structure is as follows:
1) Invoice
Invoice_no number(8),
Debtor varchar2(35),
Invoice Date Date,
Print Date Date,
Currency varchar2(3),
Total Amount Ex Number(11,2),
Total Amount Vat Number(11,2),
Total Amount Number(11,2),
Status Varchar2(15),
2) Invoice Line
Invoice_no number(8),
Invoice_Line_No number(8),
Description_1 varchar2(35),
Description_2 varchar2(35),
Line_Quantity Number(11,2),
Currency varchar2(3),
Line_Amount Number(11,2),
Vat Code number(1),
Status Varchar2(15),
3) vat code
vat code number(1),
Description varchar2(35),
Vat Percentage number(11,2)
Here invoice no and invoice line no are automatically generated fields?
How to generate invoice line no corresponding to invoice no and how to satisfy the following conditions:
1) total vat amount in invoice table should be automatically filled . total vat amount=(line_quantity* line_amount*vat percentage)/100
2) invoice cannot be printed, changed when status is 'closed'.
3) invoice lines cannot be added,changed or removed when status of invoice is closed
4) when any of invoice line no is changed,added, removed or invoice is changed the status of invoice should be changed to 'new'.
5) invoice date of a new invoice cannot be of the past
SQL & PL/SQL :: Trigger Sum Constraints
Mar 7, 2010Basically, I need to fulfil the constraint:
"student cannot have more than 100 points"
I need to use a trigger to do this, but I don't understand how this would work.
Written in plain english I imagine it work work something like:
AFTER UPDATE
WHERE student.lessonid = lesson.lessonid
IF SUM(lesson.points) > 100 then ROLLBACK
lessonid is the only field relating the two tables.
--
How to fulfill that criteria, whether it be using triggers or not.
SQL & PL/SQL :: MDB File Trigger
Jul 22, 2009I have created a link of *.mdb (Microsoft Access File) with oracle 9i database with ODBC connectivity.
Problem is i can not track any changes made in *.mdb file table. Is their any option to track any changes like insertion, updation or deletion made in *.mdb file table.
SQL & PL/SQL :: Creating A Trigger
Jul 19, 2011I 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.
SQL & PL/SQL :: Update Every Tow Trigger?
Apr 27, 2011I have one table with one row with some coefficients. If I update a coefficient from my table I want to be updated all the rows from another table. I've made a trigger but in the second table I have a column for example TOTAL and I want that column to be multiplied with my new coefficient, and I want this for every row in the second table. How I can select that total column in the trigger for every single row and update it?
View 14 Replies View Related