SQL & PL/SQL :: Make A Trigger That Don't Allow Delete?
Jun 11, 2013Well I need to make a trigger that don't allow delete departments in the next 3 month.
I dont know how to start it :/
ADVERTISEMENT
SQL & PL/SQL :: Make A Trigger On Temp Table?
Dec 27, 2011 i am trying to make a trigger on temp table(emp_rec_temp), whose data will be insert into master(emp_rec) table. But not yet get the success.
create or replace trigger trig_emp_find after insert
on emp_rec_temp for each row
declare
V_STR VARCHAR2(25);
j number;
[code]....
Delete Record In Update Trigger?
May 24, 2012the following case is successfully done with mssql databases.
Case:
Table UserGroup
Columns id, name, handshake
When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.
What have I done so far:
- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.
- created a view in combination of instead of update trigger.
This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.
Code so far:
create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE)
is
begin
if p_handshake = 'd' then
delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
commit;
end if;
end;
create or replace view USERGROUP_V as select * from USER_GROUP
create or replace trigger USER_GROUP_T1
instead of update on USERGROUP_V
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE);
end;
Insert / Update / Delete Trigger Creation?
Feb 6, 2009I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so
Id_num varchar (20)
code Integer
desc varchar(20)
sequence_num Integer
audit table
Id_num varchar(20)
code Integer
desc varchar(20)
timestamp date
sequence_num Integer
Simulate Delete Operation Through Using Update On Trigger
Jul 27, 2008I'm trying to simulate a delete operation through using an update on a trigger my tables are
CREATE TABLE EMPLOYEE (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));
[code]....
What I want to do is whenever there is an update on vet( valid end time) in employee, delete the values from the employee table and insert the old values from employee into the emp_history table along with the new value for vet. Here's my trigger
CREATE TRIGGER trig4
AFTER UPDATE OF VET ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO EMP_HIST VALUES( : old.LNAME, : old.SSN, : old.salary, : old.dno, : old.vst, :new.vet);
DELETE FROM EMPLOYEE WHERE(SSN = :NEW.ssn AND vet IS NOT NULL);
END trig4;
//ignore the space between : and o as it makes a smily
The problem is I get an error for a mutating change, what I'd like to know is if the above trigger is possible, and if so how to implement it without giving me an error. I mean it makes sense syntactically and logically(at least to me).
Trigger - Delete Account Through A Java Interface Using JDBC - Get Error?
Dec 10, 2012Here are the relevant tables to the triggers I'm having trouble with.
create table profile
(
userID number(10),
uname varchar2(64),
email varchar2(32),
password varchar2(32),
date_of_birth date,
[code]...
Here's the triggers I currently have that doesn't work.
create or replace trigger DropAccount
after delete on profile
FOR EACH ROW
BEGIN
delete from groupMembership where (userID = :old.userID);
END;
The DropAccount trigger's purpose is to delete all the records from groupMembership that contains the userID of the person deleting his/her own account. It appears to work when I run the following statements in sqlplus:
delete from profile where (userID = '1');
select * from groupMembership where (userID = '1');
But when I try to delete an account through a java interface using JDBC I get the following error:
Machine Error: java.sql.SQLException: ORA-04091: table *****.GROUPMEMBERSHIP is mutating, trigger/function may not see it
ORA-06512: at "*****.DROPACCOUNT", line 2
ORA-04088: error during execution of trigger '*****.DROPACCOUNT'
The java function basically just executes a delete statement.
Forms :: Delete A Record By Writing Code In When_button_pressed Trigger
May 4, 2010How can I delete a record by writing code in when_button_pressed trigger.
View 8 Replies View RelatedSQL & PL/SQL :: Data Manipulation Insert - Procedure / Trigger Should Delete Initial Record Saved
Aug 30, 2012i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.
CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);
create sequence s_it_no start with 1 ;
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM OT_INS_ITEM;
[code]....
PL/SQL :: To Delete Child Records Manually Without Using Oracle Delete Cascade
Oct 9, 2012I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
View 9 Replies View RelatedForms :: 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 RelatedSQL & PL/SQL :: How To Make PK Not To Validate
Nov 21, 2011i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming
alter table sections_budgets modify primary key disable cascade;
OR
alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;
the insert statement which i am using. The first four columns are part of PK
insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);
is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?
SQL & PL/SQL :: How To Make Report
May 2, 2010How can I make report in below format ?
Month Apr-09 May-09 Jun-09 ...... Mar-10 Total
Basic 1000.00 1100.00 1300.00 ...... 1300.00 13260.00
HRA 500.00 600.00 650.00 ...... 600.00 6350.00
Conveyance 100.00 200.00 300.00 ...... 350.00 1300.00
Gross Amt 12350.00 13500.00 13200.00 ......14500.00 23456.00
How To Make Sure Checkbox Checked Or Not
Apr 19, 2007On a page I have let's say 5 rows (can be more or less) and each row ends with a checkbox. On the next page(next prcodure) I go through all rows, but I get a "No data found" when a checkbox is not checked. Checkbox's parametre is send using an array.How can I make sure is a checkbox checked or not. If it's not, I put a formHidden through in it's place?
View 1 Replies View RelatedHow To Make Multiple Inserts In DB
Sep 4, 2009i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.
View 2 Replies View RelatedSQL & PL/SQL :: How To Make Horizontal Partitioning
Mar 4, 2013how should i do to make horizontal partitioning?
View 18 Replies View RelatedSQL & PL/SQL :: How To Make Columns Out Of Records
Feb 8, 2013I want to learn how to make columns out of records?
View 8 Replies View RelatedSQL & PL/SQL :: How To Make A Call To A Proc
Mar 24, 2010How to make a call to a proc and immediately return without waiting for the called proc to end.
Nothing I try seems to do the trick...The calling program always has to wait.
Forms :: How To Make A Calendar
Mar 4, 2003I have read the messages below about making a calendar but am still unsure of how to go about doing this. I have the STNDRD20.OLB file, but I don't know where to put it or what to do with it! What comes next?
View 20 Replies View RelatedForms :: How To Make A Node
Feb 27, 2012How to make a node of a Hierarchical Tree BOLD without intervene java code ?
View 1 Replies View RelatedPL/SQL :: How To Make Default For Nchar
Aug 3, 2013I want to make default 'y' for COL1 how should i make it.1) "COL1" NCHAR(10) DEFAULT N'y'or 2) "COL1" NCHAR(10) DEFAULT 'y' yours sincerly
View 5 Replies View RelatedMake Some Rows As A Read Only?
Jan 8, 2013Oracle Version 11.2.0.1
Windows
There are some tables in a schema. How can I make some rows as a read only i.e. no user can make either any update nor delete them. After searching in google, I found that trigger is one way, but since this way can be manipulated by dropping or renaming the trigger, so I am looking non-trigger way to achieve this problem. Making tablespace read only affects other users. I am not looking whole table as a read only, just couple of rows should be locked (no DML allowed) for specific time period and I after specific time/date this restriction should auto disable or on user call i.e. something like row's block level locking..
PL/SQL :: Way To Make A Cursor Of Record ID
Nov 15, 2012I need to run a query that runs a stored procedure. The stored procedure takes a cursor of record ids as a parameter. I've been making the cursor like this, if there was a smarter way. I can't change the stored procedure, so this is the only thing I have control over.
CURSOR id_cur
IS
SELECT id
FROM table t
WHERE
t.id IN (
id1,
id2,
...
id500
);It just seems kind of strange to select only ids from records when the list of ids is given.
PL/SQL :: Make Index On Column?
Oct 5, 2013If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?
View 2 Replies View RelatedPL/SQL :: How To Make A Linked List
Jul 12, 2012I've seen how to implement a linked list in oracle ? but it's not what I'm interested in.
I've almost made a linked list (of chars) type, however it misses the notion of empty list and thus I virtually cannot construct it.
create or replace type LString as object
(
head Char,
tail ref LString,
member function cons(
p_x Char)
return LString
[code]...
I need to be able to make empty list to construct other lists, for example:
empty_lstring.cons('H').cons('i').cons('!')
Make Oracle Tree In Specified Format?
Apr 6, 2013---------------------------script of table for tree--------------
create table eqpt_mast
(
EQPT_ID VARCHAR2(16),
EQPT_CODE VARCHAR2(9),
PARENT_CODE VARCHAR2(9),
[code]....
output of tree like:-
grandf(0)
|__
---father1(1)
[code]...
i want to make tree like the above format.In the above tree "(value)" shows the code_type i.e. level of the tree..
version----Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
How To Make A Command Loop Three Times
Jun 28, 2013I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?
create sequence MY_FIRST_SEQ
increment by -3
maxvalue 5
start with 5
minvalue 0
nocycle;
select MY_FIRST_SEQ.NEXTVAL from dual;
SQL & PL/SQL :: How To Make Return All Records Except Duplicates
Mar 29, 2010I have a table like this basic example:
ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
Forms :: Make Calendar In Oracle 10g
Sep 15, 2011I want to make calendar in oracle form 10g, but i don't know make it. How to do make calendar?
View 2 Replies View RelatedSQL & PL/SQL :: Make Cursor To Take Select Statement?
Jul 9, 2012How to make cursor to take a select statement
then check the rowcount
in certain case: assign another selete statement to the cursor
Forms :: Make Field Non Editable
Jul 29, 2010I have a field on the form which is a database field and the value is populated by a LOV. The user should not be able to edit the value selected or enter his own values in the field. Basically the field should only be populated by the lov and should not be editable.
I tried using the SET_ITEM_PROPERTY('XX.XX', UPDATE_ALLOWED, PROPERTY_FALSE); but this only disable update against existing value. If a user selects a new value from LOV that can be edited.
