Re: In one of negative test row-level trigger results into loop

> On Monday, September 24, 2012 7:44 PM Adrian Klaver
> On 09/24/2012 07:03 AM, Amit Kapila wrote:
> > Below test results into Loop:
> >
> > 1.create test table
> >
> > CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
> >
> > 2.create trigger function
> >
> > CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS
> >
> > $$
> >
> > DECLARE
> >
> > PSQL VARCHAR2;
> >
> > BEGIN
> >
> > Raise info 'This is Test!!!';
> >
> > psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';
> >
> > execute psql;
> >
> > RETURN NEW;
> >
> > END;
> >
> > $$ LANGUAGE plpgsql;
> >
> > 3.create trigger
> >
> > CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON
> > TEST_TABLE FOR EACH ROW
> >
> > EXECUTE PROCEDURE TRIG_FUNC ();
> >
> > 4.Perform an insert statement
> >
> > INSERT INTO TEST_TABLE VALUES（'jack',25);
> >
> > Now, You will see an always loop.
> >
> > I understand that user can change his code to make it proper.
> >
> > However shouldn’t PostgreSQL also throws errors in such cases for
> > recursion level or something related?
>
> What database are you running this on?
>  I get :
> test=> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
> ERROR:  type "varchar2" does not exist
> LINE 1: CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
>

I am sorry, actually I was trying to compare behavior with Oracle so used it
wrongly.
In Create Table statement, change Varchar2 to Varchar(30)
And change in trigger function from Varchar2 to Varchar(200)

With Regards,
Amit Kapila.