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


ADVERTISEMENT

SQL & PL/SQL :: Forms 6i - Compare Start And End Dates

Feb 6, 2011

I am comparing the dates in forms 6i.I need to compare the start and end dates and if the date are getting than an year then i need to get an error message.

View 2 Replies View Related

SQL & PL/SQL :: Converting Dates - How To Compare Data With Sysdate And Display

Jan 5, 2011

CREATE TABLE SCHEDULE_DETAILS
(
SCHEDULE_ID NUMBER NOT NULL,
SCHEDULE_TYPE VARCHAR2(10 BYTE),
SCHEDULE_START_DATE DATE,
SCHEDULE_END_DATE DATE,
RUNTIME CHAR(8 BYTE),
TIMEZONE VARCHAR2(40 BYTE));

SET DEFINE OFF;
Insert into SCHEDULE_DETAILS
(SCHEDULE_ID, SCHEDULE_TYPE, SCHEDULE_START_DATE, SCHEDULE_END_DATE, RUNTIME, TIMEZONE)
Values
(1970, 'Daily', TO_DATE('07/26/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('01/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13:58 ', 'America/New_York');
[code]........

Taking Today date and timezone as EST, I need to run a select that shows all the rows , where sysdate falls in between

Start_date and End_date and RunTime, ( run time is basically the Local time of the TimeZone Column)

Basically we should Display rows by checking/Converting, Start_date||End_date||Runtime||timezone with Sysdate(est) then display.

From the Above Data these rows should be Displayed by that select, how to compare this data with sysdate and display.

SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
1970Daily7/26/20101/26/201113:58 America/New_York
2588Daily10/18/20104/18/201115:50 America/New_York
3567Daily12/8/20106/8/20118:40 America/New_York
3386Daily12/27/20106/27/20111:0 America/New_York
1973Daily8/3/20102/3/201111:25 America/New_York
2565Daily9/7/20103/7/20117:0 America/New_York
3580Daily12/20/20106/20/201117:0 America/Chicago
3167Daily11/30/20105/30/20111:0 US/Alaska
3390Daily12/30/20101/15/20117:00 Asia/Calcutta

For Example, Below rows shouldn't come, Since it's end date is less than Sysdate.

SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
2579Daily9/17/20109/18/201011:32 America/New_York

View 2 Replies View Related

PL/SQL :: Creating A List Of Dates And Hours

Jul 24, 2013

I have the following code which will produce a list of dates - 

SELECT to_date('01-Jan-2013','DD-Mon-YYYY') + rownum - 1
FROM     all_objectsWHERE   rownum <= to_date('31-Jan-2013','DD-Mon-YYYY') - to_date('01-Jan-2013','DD-Mon-YYYY') + 1 01/01/201302/01/201303/01/201304/01/2013.....30/01/201331/01/2013

 But what I'm looking for is a way to also include all possible hours with the dates, so my output would be like -

 01/01/2013 0001/01/2013 0101/01/2013 0201/01/2013 03...31/01/2013 2131/01/2013 2231/01/2013 23

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

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

Finding Individual Dates Having Dates Plus 2 Days

Jan 26, 2011

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10
1220.01.10
1321.01.10

1423.01.10

1526.01.10
1627.01.10

1729.01.10
1831.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

View 2 Replies View Related

SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?

Mar 10, 2011

Split a date into new dates according to black out dates!

Here is my tables:

CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;

[code]....

I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.

Note: Travel Date can be between 0000-01-01 - 9999 12 31

Sample:

Travel Date:

Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11

Black Out Dates:

BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29

Excepted Result:

New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11

Visually:

Travel Date : -----[--------------------------]--

A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------

Result :

X1 : -----------[--]--------------------
X2 : -----------------------[--------]--

Sample 2:

Travel Date : -[--------------------------------]--

BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------

Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--

How can I do it using PL SQL ?

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

Select All Dates Between Two Dates

Oct 22, 2003

How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

12/2/2003
12/3/2003
12/4/2003
12/5/2003

Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.

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

How To Compare A Value In SQLLDR By Using WHEN

Feb 25, 2013

Can we compare in SQL *Loader control file by using WHEN Clause.I want to load the data when in_no greater than 1300000000. While running below control file i am getting error as:

SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
WHEN (in_no >= '1300000000')
Here is the control file.

ex:

Load Data
infile *
discardfile 'test_when.dsc'
truncate into table test_when
WHEN (in_no >= '1300000000')
fields terminated by ','
(a,b,c,
in_no)
[code]....

how to compare a value in sqlldr control file.

View 1 Replies View Related

SQL & PL/SQL :: Compare Two Different Table?

Jul 11, 2011

I want to compare two tables column by column and find out which columns are matching and inserting matching columns value into another table.

View 1 Replies View Related

SQL & PL/SQL :: How To Compare The Four Tables

Jan 5, 2013

I Have Four Tables

1) Sal_master
structure is voc_no varchar2(7),voc_date date

2) sal_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

3). delivery_master
structure is voc_no varchar2(7),voc_date date;

4) delivery_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example

Voc_no Sale Qty Deliver Qty Remaining Qty

S000075 10 5 5 if data not found from delivery master and detail then answer must be
S000075 10 0 10

View 2 Replies View Related

SQL & PL/SQL :: To Compare Two Strings

Apr 8, 2008

I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match

Do we have any oracle built ins to compare ?

View 6 Replies View Related

SQL & PL/SQL :: How To Compare Time

Jun 30, 2010

Checkout the following code

CREATE TABLE CHK(dt_request_datetime DATE);

INSERT INTO CHK VALUES(TO_DATE('25-JUN-10 04.12.57 AM',
'DD-MON-YY HH:MI:SS AM'));

COMMIT;

[Code].....

TZ_OFFS TZ_OFFS GMT_TIME
------- -------
-04:00 +00:00 25-JUN-10 04.12.57.000000000 AM

EST_TIME
-----------
25-JUN-10 12.12.57.000000000 AM EST

9 am
----
25-JUN-10 09.00.00.000000000 AM EST

8 pm
-----
25-JUN-10 08.00.00.000000000 PM EST

OUTPUT
---------
30-JUN-11

My doubt here is that even though dt_request_datetime is between 9 am in EST timezone and 8 pm in EST timezone the query output is not according to that.

View 2 Replies View Related

SQL & PL/SQL :: How To Compare Hours

Jan 10, 2011

I want to write a function that gets:

1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)

I got a table called EVENTS that got 2 fields:

1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)

and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:

input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date

but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..

I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.

View 2 Replies View Related

Compare Two Columns Relationship

Apr 29, 2011

I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.

Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058

I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b

where a.Col1 = 11060
and a.Col1=b.Col1
and a.Col2 <> b.Col2
order by Col1, Col2
[code]....

HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.

View 1 Replies View Related

How To Compare 2 URLs And Convert Into 1

Apr 12, 2008

[URL]....

[URL].....

i want to compare this URL and convert them into 1

i.e any url containing rediff word i want to convert that URL into

[URL]......

View 3 Replies View Related

Compare Two Time Values In Sql?

Dec 4, 2009

is it possible to compare two time values in oracle sql ie there is a column say 'tot' with values 8:29,11:35 etc(8hrs29 mins etc) can i compare this column with 03:00hrs i tried select case tot>=3 then 1 as days end from tablename;

View 3 Replies View Related

Compare Records In A Recordset?

Oct 28, 2010

Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.

I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.

After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.

The problem is: how can I not use a temporary table. I've search through online about using nest tables. but somehow I just could not get it working.

how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.

View 3 Replies View Related

SQL & PL/SQL :: Compare Varchar With Date

Feb 26, 2012

I have one command compare varchar type with date type by Pl/SQL.

date_entry gave values:" 2012/01/06 22:28:24",

now i want to convert it to date and compare with sysdate.

Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24

after I executed that command It's appear one message .

View 7 Replies View Related

SQL & PL/SQL :: Compare Columns Of Two Table?

Apr 5, 2013

I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..

View 17 Replies View Related







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