How do I write a unique constraint as a trigger?
<p>I am creating a new table in an Oracle database, and I am trying to learn how to create triggers.  To start, I figured I would try to create a basic trigger that checks for existing values prior to insert or update.  Down the road, this will be handled as a constraint, but I'm also going to need some triggers that compare values between other existing rows and columns, so I would like to know how I would write this as a trigger.</p>

<p>What I am trying to achieve:  Create a trigger that only allows a row to be created if no other rows in the table contain the identical TEMPLATE_ID and PRE_TEMPLATE_ID values that are attempting to be entered in the new row.</p>

<p>My code:</p>

<pre><code>CREATE TRIGGER check_redundancy
BEFORE INSERT OR UPDATE
ON SCHEMA.TABLE
FOR EACH ROW
BEGIN
    IF NEW.TEMPLATE_ID NOT IN
        (SELECT
            A.TEMPLATE_ID
        FROM
            SCHEMA.TABLE A
        WHERE
            NEW.PRE_TEMPLATE_ID = A.PRE_TEMPLATE_ID)
    THEN
        INSERT INTO SCHEMA.TABLE (ID, TEMPLATE_ID, PRE_TEMPLATE_ID, DATE_CREATED, CREATED_BY)
        VALUES (NEW.ID, NEW.TEMPLATE_ID, NEW.PRE_TEMPLATE_ID, NEW.DATE_CREATED, NEW.CREATED_BY);
    END IF;
END;
</code></pre>

<p>When I run this, I receive the following errors:</p>

<p>"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
"ORA-06512: at line 14"</p>

<p>(worth noting - it always is telling me that error at line 14... even if i remove everything between the BEGIN and END, it still says that error at line 14).</p>

<p>So I assume there is a variable (or variables) that I am supposed to be declaring somewhere, but I'm not really sure how to do that.</p>