im trying to create a view on a table :
CREATE TABLE sample_test
)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 )
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.
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
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.
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)
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.