Home » SQL & PL/SQL » SQL & PL/SQL » Beginner: How to write this trigger?
Beginner: How to write this trigger? [message #393260] Fri, 20 March 2009 21:45 Go to next message
Messages: 1
Registered: March 2009
Junior Member
I have create a table called SUBSCRIPTIONS(Sid,Uid,Plan,Date).

I want to check when I insert a record, I don't want a uid appears less than once or more than three times.

Otherwise, user raise_application_error(-20000,'Insert Error.') to report that it is invalid.

Thank you very much.

[Updated on: Fri, 20 March 2009 21:46]

Report message to a moderator

Re: Beginner: How to write this trigger? [message #393261 is a reply to message #393260] Fri, 20 March 2009 22:31 Go to previous messageGo to next message
Messages: 25570
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Beginner: How to write this trigger? [message #393262 is a reply to message #393261] Fri, 20 March 2009 23:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, in light of the fact that the table you describe could not easily be created as you have described it, one must wonder if you actually did any real work before posting your question.

Be that as it may, I will stick my neck out and offer up one possible solution. There will naturally be many who won't like it. Remember the saying "Lead, Follow, or get out of my way!".

I fancy instead-of-triggers so I am going to offer you up an instead of insert trigger that will do part of what you want. I leave it to you to write the update/delete triggers for yourself. I am trying to teach here, not do your work for you. Should someone find a flaw in what I have written, please comment on it. I believe this works but hey, mistakes happen. Better for Maximum and myself to find out sooner than later. If however your opinion is just that "you don't like it" then you better put up or shut up. First acknowledge that this will work (or will not work (better explain why you think it won't)), then offer up your alternative (you do have an alternative don't you), then tell us why yours is better than mine. I am all for constructive criticism, but I have had it up to my eyeballs at work of late with turkeys who have nothing valuable to contribute of their own.

If you have no working alternative that gives the customer what they want, then by defacto my alternative is infintely better than yours no matter what you might not like about it. Boy I am on a roll tonight. Without further ado, here it is.

-- UID is a reserved word I think so I have renamed the column
-- infact several of your columns are reserved words (gessh)
-- fix that will you
create table SUBSCRIPTIONS
   aSid number not null
 , aUID number not null
 , aPlan number not null
 , aDate date not null

create index SUBSCRIPTIONS_pk on SUBSCRIPTIONS (asid)

   add constraint SUBSCRIPTIONS_pk primary key (asid)


create or replace view SUBSCRIPTIONS
select *

-- a generic locking table
create table user_lock
  message varchar(255) not null

create index user_lock_pk on user_lock (message)

alter table user_lock
   add primary key (message)

create or replace trigger ioi_SUBSCRIPTIONS
instead of insert on SUBSCRIPTIONS
for each row
   message_v varchar2(255);
   v1 number;
begin null;

-- first take care of locking
-- this is the most important step yet 99% of programmers do not understand they need to do it
-- in a multi-user environment we must serialize on the aUID
-- in order to prevent transactions working with the same aUID from interfering with each other
-- by creating our own locked row specifically for the aUID, we serialize interleafing transactions
-- thus letting only one transaction work with any one aUID at a time
-- this reduces concurrency when dealing wih "HOT" aUID values, but ...
-- by doing this locking, we ensure the count we do as a check here is correct for our entire transaction
-- and thus we can reliably enforce or cardinality rule
-- this lock will remain till we commit or rollback
   message_v := 'locking aUID='||:new.aUID;
      insert into user_lock values (message_v);
   exception when dup_val_on_index then
         select cast(null as number)
         into v1
         from user_lock
         where message = message_v
         for update
      exception when no_data_found then
         raise_application_error(-20999,'trigger:ioi_SUBSCRIPTIONS:expected user_lock row not found:(message_v)=('||message_v||')');

-- now that we have serialized transactions working with the same aUID
-- it is safe to count the number or aUIDs currently in the table
-- we will not have any trouble with getting the correct count
      select count(*)
      into v1
      where aUID = :new.aUID
      and rownum <= 3
      having count(*) = 3
      raise_application_error(-20999,'trigger:ioi_SUBSCRIPTIONS:aUID already exists three times:(aUID)=('||:new.aUID||')');
   exception when no_data_found then null;

-- if we get to this point, we are good to insert the row
   insert into SUBSCRIPTIONS_d values (:new.asid,:new.aUID,:new.aplan,:new.adate);
show errors

Some things worthly of note in the above code:

1) we do locking for multi-user environments. This is necessary to insure that the count we get when we look to see if we are breaking the rule, stays true till our transaction is done. Without it we run the risk in various scenarios of getting a count that would let us add a uid when we should not or stop us form adding when we should. Think about concurrent transactions working with the same UID and how Oracle's multi-versioning lock strategies affect your count.

2) we achieve our lock using a general locking table into which we put a well constructed message. This works easy enough and provides some level of auditing. The message used must be unique to the lock. Thus we would likely give it a real name when concat'ing the UID to it, instead of the generic string we pass in, but this was a quick hack to demo the idea.

3) wherever I have used raise_application_error, I have tried to construct a consistent error message with the same number of parts. I should really use a message builder procedure but again I was in a hurry.

** the instead-of-trigger will count the number of occurrances of the UID in the table and if < 3 will do the insert, if it counts three it will not allow the insert and will raise an exception instead. If there are more than three of the same UID in the table the tirgger will still stop the insert, for all we need to count is the first three before we know we have a problem. Of course if the trigger has been in force, we should not have more than three should we.

** if you do not know what instead of triggers are, then google for it.

Good luck, Kevin
Re: Beginner: How to write this trigger? [message #393289 is a reply to message #393260] Sat, 21 March 2009 01:44 Go to previous message
Michel Cadot
Messages: 65138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Carefully read and understand the following topic:

Previous Topic: Need help to get the right query to generate report
Next Topic: error while insert
Goto Forum:

Current Time: Thu Aug 17 21:59:47 CDT 2017

Total time taken to generate the page: 0.20716 seconds