CODEWITHSUNDEEP
sqloracle-databaseplsql
Romin Adi Santoso
Romin Adi Santoso

Reputation: 3

trigger with if and timestamp

I have a problem with my trigger...

here is my trigger :

  create or replace
  trigger limit_post
  before insert on post
  for each row
  declare 
     temp timestamp(6);
  begin
     temp := (select post_time 
                from post 
               where post_id = (select max(post_id) 
                                  from post 
                                 where user_id = :new.user_id));
     if day(:new.post_time) =  day(temp) 
     then
        :new.item_name := 'yeah!';
     end if;
  end limit_post;

that trigger is false... I want to create a trigger that could check if the last post_time day of that user is the same with day of today.....

if that's same so item_name is 'yeah!'

need help here.. thx... !!

Upvotes: 1

Views: 126

Answers (1)

Rene
Rene

Reputation: 10541

I would think that that trigger would throw a: ORA-04091: table XXXX is mutating, trigger/function may not see it.

You can not execute DML on the same table as the trigger is on.

If what you are trying to achieve is to limit the posts to 1 post per person per day you can create a unique key on the table on user_id and day(post_time).

Upvotes: 3

Related Questions