Unable to create a Before Trigger

Database
Enthusiast

Unable to create a Before Trigger

Hi

im trying to create a view on a table :

CREATE TABLE sample_test

(

id INTEGER

,name VARCHAR(200)

)NO PRIMARY INDEX

REPLACE TRIGGER SAMPLE_TRGG

BEFORE UPDATE OF (name) ON SAMPLE_TEST

REFERENCING OLD AS oldtable

NEW AS NEWTABLE

FOR EACH ROW

WHEN ( (SELECT COUNT(name) FROM SAMPLE_TEST,newtable where name = newtable.name) > 0 )

ABORT ;

when i try to create the trigger, it is showing me an error

a Triggered action statement contained an invalid reference .

any one can please help me out in resolving this error.

12 REPLIES
Enthusiast

Re: Unable to create a Before Trigger

any one please suggest me

Enthusiast

Re: Unable to create a Before Trigger

any suggestions

Supporter

Re: Unable to create a Before Trigger

You posted a rather incomplete question with incomplete information.

I have no clue what you want to achive here.

Enthusiast

Re: Unable to create a Before Trigger

BEFORE TO UPDATE  I WOULD LIKE TO CHECK WHETHER THE SAME NAMES ARE ALREADY EXISTING IN THE TABLE, IF EXISTING ABORT THE  ACTION.

for this i had created a table and  trying to create a trigger on that table.

But creation of trigger is failing.

please suggest on the same

Enthusiast

Re: Unable to create a Before Trigger

any one would please suggest me, its a little bit urgent

Supporter

Re: Unable to create a Before Trigger

Do you considered to use a Unique secondary index on name?

I am not sure that Trigger are the best way to enforce RI here...

Depending of the workload on the table you will face bad performace - each update will be a full table scan.

Senior Apprentice

Re: Unable to create a Before Trigger

Another question comes to mind:

Why do you create a NoPI-table, when you need a unique constraint on it?

As Ulrich already said, each and every SELECT/UPDATE/DELETE results in a Full Table Scan.

A UPI will be much more performant than this trigger (and you need another INSERT trigger, too)

Dieter

Enthusiast

Re: Unable to create a Before Trigger

Agree with you, but  customers will updates the name through a web interface. the web interface will behave in such away that if we define the INDEX or Constraint those columns are not editable via website.

So im not using any INDEX  here, i would like to check if there is any other option to do the same in teradata.

would you please suggest me how to restrict customers not to enter the duplicates.

Enthusiast

Re: Unable to create a Before Trigger

Any one please suggest me little bit urgent.