We 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 RepliesHow can "call one trigger of item in trigger of form"
View 5 Replies View RelatedCan we call trigger in another trigger,if it is possible any example.
View 1 Replies View RelatedCREATE 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.
I 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.
There 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 RelatedI 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.
consider 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?
I 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
Basically, 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.
I 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.
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.
I 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 RelatedI have a table which has a trigger defined as such on the Main table.
before INSERT OR UPDATE OF USER_NM,COmpany_ID,UPDATED_BY_USER
OR DELETE ON MAIN TABLE OLD AS OLD NEW AS NEW
FOR EACH ROW
When I insert the 1st row (by user Scott)
MAIN TABLE
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------
ABC1Scott
In the audit table I have this information:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
When an ADMIN user (other than Scott deletes the above row)
MAIN TABLE:
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------row deleted....
In the audit table i have 2nd entry as:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
ABC 1 ScottDELETE
Here the updated_by_user is Scott and not ADMIN, even though I am doing the following in the trigger.
UPDATED_BY_USER := sys_context('USERENV','OS_USER')
I want to show the user who has deleted the record which should be ADMIN.
Since the user deleting may not be the samae as the one who inserted.Any clue?
I have an table where are three cols:
ID, DECRIPTION and HOX, are nullable columns.When inserted ID on table, description will automatically updated by the trigger as 'NOT VALID', now I need modification on the trigger that if the IDchanges from 1000, (the HOX-value stays at it was) to some other value like 1001 description willupdated to 'VALID'. But if the ID is not 1000, like 999,description will stay as NOT VALID.
how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing
View 19 Replies View Relatedi have a table with 46 columns
i want to fire update trigger except update on one column (named EMP_ID)
how to write ???
i tried this but this is too Large
create or replace trigger aft_update_employee
after update of [ that 1 to 45 columns other then emp_id ] on employee
(this query is so large )
I have the following problem.
I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. The problem is:
When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.
Here are the code to simulate this problem:
--Table
create table TB_TEST
(
ID NUMBER(10) not null,
FLAG NUMBER(10) not null,
TAG VARCHAR2(16) not null,
TS_ATU_DTR TIMESTAMP(9) not null
);
[Code]..
Testing
exec test1;
select * from textolog order by data, texto;
Output:
DATA TEXTO
--------- ----------------------------------------------------------------------------------------------------
15-FEB-11 1:old.FLAG=123, :new.FLAG=321
15-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
15-FEB-11 1:old.TS_ATU_DTR=, :new.TS_ATU_DTR=
15-FEB-11 2:old.FLAG=123, :new.FLAG=321
15-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
15-FEB-11 2:old.TS_ATU_DTR=, :new.TS_ATU_DTR=15/02/2011 11:07:38.094284000
-----
"old.TS_ATU_DTR=, " Isn't it right?????
Why the other fields aren't null?
I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it?
Or, is there a patch for this problem?
The below trigger is running fine without any error ,but i wasn't able to perform the exact logic by the below trigger.IF, inserting multiple row then the SIVNUM field gets the same sequence number for each row , without incrementing the value for the next row.
Only the sequence value increases on the next set of row ow's inserted
Example (inserting 2 rows ) based on the below trigger:
item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00002
i want to achieve :
item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00001
CREATE OR REPLACE TRIGGER siv_mat_insert
BEFORE INSERT
ON MATUSETRANS
REFERENCING OLD AS old NEW AS new
for each row
[code]....
I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.
Here are the code to simulate this problem:
--Table
create table TB_TEST
(
ID NUMBER(10) not null,
FLAG NUMBER(10) not null,
TAG VARCHAR2(16) not null,
TS_ATU_DTR TIMESTAMP(9) not null
);
[code]...
Why the other fields aren't null?I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it? I am using Oracle 11.2.0 - Suse Linux.
I have a table known as Registration in which there are columns StudentID, ModuleId, year, semester
semester = 1 or 2
I want to write a trigger so that student can register on a maximum of 4 modules per semester
i.e.
select count(*), semester from Registration where studentid='241234' group by semester
I tried the following trigger but I am stuck
If I dont use "for each row", I am not allowed to use :new and ld and when I use "for each row", count would be for each row which would be 1 or 0
create or replace trigger checkmodulecount
before insert on Registration
DECLARE
noCourse NUMBER;
nCount NUMBER;
FAILED EXCEPTION;
[Code]....
WHEN FAILED THEN
raise_application_error(-20000,'Cannot reigster for more than four courses');
END;
i want to run a Trigger on daily morning 9 A.M.
Now i am running it manually at 9 A.M without checking systime from dual
is there any solution to run a trigger automatically on particular time daily ?
How to make a trigger of changing value. For example I have a table of customer .
Customer_id, Cumtomer_name, cus_date and stats.
Values
102, ABC,02-06-2012 and Open
Now tell me how I can make trigger after two hours stats change OPEN TO CLOSE.
I have written small procedure here...
I used two tables in my procedure..TBLEMP and TBLORG..
STEP I :->
I try to insert record in both table TBLEMP and TBLORG...I written seperate BEGIN and END block for TBLEMP and TBLORG Insert operation...When some exception is occured then I rollback all the data..if not then insert statements are commited..
STEP II :->
On table TBLORG one trigger is written..that will execute on each new insert...In this trigger I get the current record(Current employee) data from TBLEMP
Now my question is for executing trigger is it necessary to commit the TBLEMP operation, before getting data from TBLEMP.
I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.
CREATE OR REPLACE PROCEDURE deepa.myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
[Code].....
This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema
like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.
I just create a trigger to fire whenever the quantity on hand for a books table has been updated.when the quantity on hand become zero ,it should call 'insert_reorder' procedure and pass the ISBN for the books. This is my code
CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN
IF :NEW.on_hand_quantity = 0 THEN
insert_reorde_pp (:NEW.isbn)
END IF;
END;
But i m getting this error
LINE/COL ERROR
4/3 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.
I cant create a trigger for the following problem "write a trigger to change the "hiredate"(in emp tale) in the default format even if the user enters it in some other format, say year/mon/date..."
View 7 Replies View Relatedhow to know the trigger event name in trigger calling...like-
CREATE OR REPLACE TRIGGER ******
BEFORE DELETE OR INSERT OR UPDATE
OF TOTAL_QUANTITY,CANCELLED
ON *********
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
-----------I want to know given trigger event at this level-----------
IF INSERTING THEN
null;
ELSIF UPDATING THEN
null;
ELSIF DELETING THEN
null;
END IF;
END;
I disable the triggers when I program. what the program is doing behind the scenes. So if I insert into a table, the trigger would fire and insert into another table. Disabling the triggers shows me that I need to insert into both tables. This also allows me to have the primary key in my conversion tables match the database.
With that being said, can I tell the trigger to fire after the fact?
We 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..