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

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);

>
> With Regards,
>
> Amit Kapila.
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com