SQL & PL/SQL :: Trigger Where Sysdate Can Be Automatically Written Into A Table?
Sep 11, 2012
I just want to know whether can i create a trigger where the sysdate can be automatically written into a table? for ex: if to_char(sysdate,'HH24:MI:SS') = 18:00:00 then it should be written in a table..?? is there anyother way instead of triggers??
I have created a datablock and set up the user interface to allow the users to insert all the data they need, however my table has some fields which should be generated automatically and are not done through user input:
id - which is uniqe to each entry to the table so is gernerate by a sequence (which I created on the database) but when i put seq.nextval into the initial value on the datablock it says i cannot do this, so how do I use the sequence to insert the value into that field in the database. This is my primary key in my database whichobviosuly my users cant input data to, instead it is generated automatically.
User - similarly in the datablock is a user field which states the user that inserted that record, I know on the database i can put DEFAULT user for that field , but how do I make the user insert automatically through forms.
Date - Similarly again how do I set SYSDATE to be automatically input to the date field when the user submits the record?
SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES 2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW 3 DECLARE 4 v_fno NUMBER; 5 v_tab VARCHAR2(30); 6 v_unique_id VARCHAR2(40); [code]....
Trigger created.When I am trying to insert into complements table it is throwing error as follows:
SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL); insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL) * ERROR at line 1: ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function may not see it ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19 ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'
I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.
I have a table which contains 2 main columns apart from other columns. These two columns are REC_ID and ENTRY_DATE_TIME.
In this table data is inserted through a data load process using SQL Loader. REC_ID is populated to table through a sequence and ENTRY_DATE_TIME is populated as a sysdate.
select rec_id, to_char(entry_date_time, 'dd-mon-yyyy hh24:mi:ss') entry_date_time from itpcs_grt_oa_tran_rdpk_stock order by 1;
If we see the data, then time for REC_ID 4 is less than REC_ID 3.
Now the question is if REC_ID is populated through a sequence then sequence 3 must have been created before REC_ID 4 and if so then why the ENRTY_DATE_TIME of REC_ID 4 is less than REC_ID 3.
Further to the question, how data is written in tables. What steps are performed by at database level when we any DML(insert) is performed.
My DB Version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
I have two schema named ODI_MASTER and ODI_WORK.Under ODI_WORK there are some tables like TEMP1, TEMP2 Further more when any new tables will create under ODI_WORK, then i need automatically grant select permission to ODI _MASTER schema.
for this purpose i choose trigger, and a Stored procedure.
CREATE OR REPLACE TRIGGER ODI_WORK.TRIG1 AFTER CREATE ON ODI_WORK.SCHEMA ENABLE call sp1 (ora_login_user)
[code]...
I searched a lot over blogs, if EXECUTE IMMEDIATE commands exist under Trigger it gets problem. Insert/update/delete statement on trigger seems no problem.
I'm trying to select some data from table base on SYSDATE. The below query does not return any data.
My query is:
select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate
why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?
Region Code,Name,Surname,ID_Number 1,John,Doe,67 1,Sarah,Jason,45 2,Bob,Marley,69 3,Steven,Johnson,900 2,Harry,Potter,890 3,Sandy,Bay,567 3,Else,Taylor,789 .....
I have about over 100 region codes and each region would like to get their own data. I can manually do the extraction and create a new table for every region, but it's going to take too long.
I'm working on a java program connected to a Oracle 9i db. I'm inexperienced about Oracle specific capabilities. The experienced Oracle users
I would like to know if there's a way to create a temporary table so that: - the table will be deleted automatically when a specific db connection closes (obligatory) - the table is visible for just one specific connection (optional)
What I would like to do is: 1 - Get the result set for query A. 1a - process the results 1b - store the results in a performance friendly way ( I thought about a temp table) 2 - Run query B over result set A. 3 - Run query C over result set A. X - The stored result set A has to be removed before/when the program ends (ideally when the db connection closes).
The problem isn't how to do this in java. The problem is to do it in a way so that the table will be automatically removed when the db connection used to create it will be closed so that's unimportant if the java program crashes or ends normally.
i have emp table of 5 columns empno,ename,dept,sal,edit_timing(edit timing datatype is timestamp).and my form contains empno,ename,dept,sal. is if i insert a record into forms it automatically insert the date and timing into edit_timing columns.
I have a table with table_ID, date_created, user_id.
I have sequence, and a BEFORE INSERT trigger, which uses the seq to increment the table_ID by 1, everytime the webpage is saved.
ques: (1).If i have date_creted defaulted to sysdate in the table, do I need to have it in the trigger? -- Update create_date field to current system date :NEW.DATE_CREATED := sysdate;
(2).How can I insert the user_id in the table, each time user SAVE the page ? web page procedure is getting the user info at the beginning. can i add it in the trigger
I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.
I am trying to find if there is a way to know how much data has been written on tape on a 'in progress' rman backup. rman session is also connected with recovery catalog.
is there a way to find-out how much data is transferred per rman channel onto tape? what is backup speed we are getting?
I want written English characters only in text item (I want control in one text item), in my form 10 g but, in same form can be written other language in other text item.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
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 .
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]...
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.
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
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.
I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.
Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.
The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.