Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    21

    Question Unanswered: New to creating triggers which enfore data integrity

    I am trying to create a trigger that will ensure that only one record in a table will have that character. Basically enforce uniqueness with a trigger. Any syntax hints will be helpful.


    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use an index. It will perform far better than a trigger.

    Andy

  3. #3
    Join Date
    Oct 2012
    Posts
    21
    I had proposed that with the developer but they insisted on a trigger as the business requirement. Any other idea?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why does it have to be a trigger?

    Andy

  5. #5
    Join Date
    Oct 2012
    Posts
    21
    I am still waiting on a response. But for now, the requirement is trigger. Let me know if you could aid with this build.

    Would this work?

    CREATE TRIGGER "xx"."xx"

    NO CASCADE BEFORE INSERT

    ON "tt"."tt"

    REFERENCING

    NEW AS newrow

    FOR EACH ROW

    WHEN ((EXISTS ( SELECT col FROM tt.tt WHERE col2 = 'A' )) and newrow.col2 = 'A')

    SIGNAL SQLSTATE ' ' ('There is already an active budget cycle. '|| 'Only one can be active ');


    Thanks for responding.

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I had proposed that with the developer but they insisted on a trigger as the business requirement.
    Developers do not determine business requirements . . .

    It would be wise to find out why someone believes this should be done using a trigger. Do they think that every edit of this type should be done by a trigger? Is your single-character example representative of the real world? Can the table only have 26/36/whatever rows (so that they will be unique)?

  7. #7
    Join Date
    Oct 2012
    Posts
    21
    After talking with the developer. There is no way a trigger can provide a solution to their requirement.

    I will comment on this if anything changes. For now the build is on hold till the app code/logic is worked through

    Thanks all for input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •