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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Table with System Default

I have a table with a system default attached to a date field - each time I insert a record and the date is passed as null the field automatically gets populated with the current date time stamp - I need someway of removing that trigger.

  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
Is it a trigger or a default column value?

If a trigger, just find it and drop it (assuming this is all it does).
If a default value, just set it to null.
drop table tab1 purge;
create table tab1(col1 char(1), col2 date default sysdate);

insert into tab1(col1) values('a');
select * from tab1;

alter table tab1 modify col2 default null;

insert into tab1(col1) values('b');

select * from tab1;

Open in new window

Table name: YOUR_TABLE
Owner of the table: MY_USER

1) Find the trigger name.

select trigger_name from user_triggers
where table_owner = 'MY_USER' and table_name = 'YOUR_TABLE';

2) If you have just one trigger then you should either disable the trigger or drop it.

To disable it you should execute: alter trigger MY_USER.<TRIGGER_NAME> disable;

To drop it you should execute: drop trigger MY_USER.<TRIGGER_NAME>;

3) If you find more than one trigger then more troubleshooting is required.

slightwv (䄆 Netminder) Commented:
>>If you have just one trigger then you should either disable the trigger or drop it.

Before you just disable it or drop it (already suggested), you need to verify setting this date is all it does.

It might do a lot more that might impact your system.

Featured Post

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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now