Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Trigger after insert?

Posted on 2011-04-25
10
Medium Priority
?
768 Views
Last Modified: 2012-05-11
I have the below trigger.

CREATE OR REPLACE TRIGGER MYSCHEMA.MYTABLE_INSERT_DATE
AFTER INSERT 
	ON MYSCHEMA.MYTABLE for each row
begin
    select sysdate INTO :new.datetime FROM dual;
end;
/

Open in new window


It works great, but I need to change it to where if the value inserted did not have a date/time value in the insert (NULL) then it will add the current date/time. But if it had a date/time value inserted it will keep the value it already had.

Thanks!
0
Comment
Question by:KGNickl
  • 5
  • 4
10 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35460423
CREATE OR REPLACE TRIGGER MYSCHEMA.MYTABLE_INSERT_DATE
AFTER INSERT
      ON MYSCHEMA.MYTABLE for each row
begin
    :new.datetime := nvl(:new.datetime,sysdate);
end;
/
0
 
LVL 5

Author Comment

by:KGNickl
ID: 35460471
CREATE OR REPLACE TRIGGER MYSCHEMA.MYTABLE_INSERT_DATE
AFTER INSERT
      ON MYSCHEMA.MYTABLE for each row
begin
    :new.datetime := nvl(:new.datetime,sysdate);
end;
/

Does not do what I need. If I insert a value it overwrites the value with the current time. 

I need it to leave the inserted datetime alone and only insert the current date/time if the datetime value inserted was empty/null.

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35460494
Try a before insert trigger
drop table tab1 purge;
create table tab1(datetime date);

CREATE OR REPLACE TRIGGER tab1_trig
BEFORE INSERT 
      ON tab1 for each row
begin
    :new.datetime := nvl(:new.datetime,sysdate);
end;
/ 

insert into tab1 values(null);
insert into tab1 values(to_date('01/01/2001','MM/DD/YYYY'));
commit;
select * from tab1;

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:KGNickl
ID: 35461165
The before and after inserts did not work. Both just wrote over the date that was provided. I think I need some type of if statemtn in the PL/SQL that checks the current rows date value for null. Then inserts current date or does nothing based on if / else outcome. I just don't know PL/SQL or how to use it in a trigger to retrieve the current rows values.

begin  
IF <DATE IN CURRENT ROW IS EMPTY/NULL> THEN
--INSERT DATE
    :new.datetime := nvl(:new.datetime,sysdate);
ELSE
--DO NOTHING
end;  
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35461178
an insert trigger files when new rows are inserted.  You can only manipulate that rows value.

Are you wanting to look in the table for other rows then take an action based on that?

Please post some sample data and expected results.
0
 
LVL 5

Author Comment

by:KGNickl
ID: 35463925
If the insert row the trigger is on has a value for datetime in the INSERT statement I want to use that value. If the insert does not have a datetime value I want to user the current system date/time. Currently, the code I have inserts the current system date/time into the datetime column no matter where the insert contained a value or not. Basically, the field is optional and if a value is provided there is a reason (such as backdating). But all other inserts that are done by applications do not contains a date in the insert and use the trigger as a way of auto timestamping.

Is this possible?
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35463941
KGNickl,,
- instead of using trigger, why not specify a default SYSDATE value for the date column? so whenever data is inserted into MYTABLE, if you supply the date value, it will take the value you supplied, otherwise  sysdate will be inserted.

ALTER TABLE MYTABLE MODIFY (datecolumn DATE DEFAULT SYSDATE)

Open in new window

0
 
LVL 5

Author Closing Comment

by:KGNickl
ID: 35463963
Perfect! Just deleted my trigger and used that. Exactly what I wanted to achieve!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35463966
The code I provided earlier in the before insert trigger should do exactly what you described.

Please add to or explain where it doesn't work.

The default column values should work as well.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35463970
Please explain the difference?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question