All Forums Database
sunny.j 90 posts Joined 10/10
27 Jan 2012
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.

 

 

sunny.j 90 posts Joined 10/10
27 Jan 2012

any one please suggest me

sunny.j 90 posts Joined 10/10
29 Jan 2012

any suggestions

ulrich 816 posts Joined 09/09
29 Jan 2012

You posted a rather incomplete question with incomplete information.

I have no clue what you want to achive here.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sunny.j 90 posts Joined 10/10
30 Jan 2012

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

 

 

 

sunny.j 90 posts Joined 10/10
30 Jan 2012

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

ulrich 816 posts Joined 09/09
30 Jan 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
30 Jan 2012

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

Dieter

sunny.j 90 posts Joined 10/10
30 Jan 2012

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.

 

 

sunny.j 90 posts Joined 10/10
30 Jan 2012

Any one please suggest me little bit urgent.

sunny.j 90 posts Joined 10/10
30 Jan 2012

any suggestions please

ulrich 816 posts Joined 09/09
30 Jan 2012

can't understand this. 

espacially if you want a change via a website - which means some kind of OLTP processing - you should not use NO PI here as it means always full table scans. 

So either 

 

CREATE TABLE sample_test

(

id INTEGER

,name VARCHAR(200)

) unique primary index (name);

 

or 

CREATE TABLE sample_test

(

id INTEGER

,name VARCHAR(200)

) unique primary index (id)

unique index (name) 

;

should work fine - if the application is doing checks here you should rather redesign the application.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
30 Jan 2012

I REALLY don't think you should implement this!!!

 

CREATE TABLE sample_test

(

id INTEGER

,name VARCHAR(200)

) unique primary index (id);

 

 

CREATE TABLE unique_name

(
name VARCHAR(200)

) unique primary index (name);

 

 

CREATE TRIGGER RaiseTrig
AFTER UPDATE OF name ON test_db _uli.sample_test
REFERENCING OLD ROW AS Oldrow NEW ROW AS NewRow
FOR EACH ROW when (1=1) insert into unique_name values (NewRow.name);

 

 

Insert into sample_test values (1,'dummy');
Insert into sample_test values (2,'dummy');
update  sample_test  set name = 'test' where id = 2;
update  sample_test  set name = 'test' where id = 1;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.