Cursor in Trigger - with cursor not open when running second time in the same SQL session 
Author Message
 Cursor in Trigger - with cursor not open when running second time in the same SQL session

I am converting a Oracle Trigger to SQL SERVER trigger and have probelm.
The Oracle program looks like this:

    TABLE a1:                                                Table
a1_result:
            col1        INT                                        c1    INT
            col2        INT                                        c2
VARCHAR(10)

    CREATE TRIGGER  trig_A1
        AFTER INSERT OR UPDATE OR DELETE ON a1
        REFERENCEING
            NEW AS NEW
            OLD AS OLD
      FOR EACH ROW
     BEGIN
     IF INSERTING THEN            -- when inserting
             INSERT INTO a1_result VALUES (:new.col1,'I')
     ELSIF UPDATING THEN     -- when updating
            INSERT INTO a1_result VALUES (:new.col1,'U')
    ELSE                                        --  when deleting
            INSERT INTO a1_result VALUES (:new.col1,'D')
   ENDIF
   END

In oracle, it has resever word "INSERTING", UPDATING and DELETING to check
the status of
what condition cause the trigger to fire. Also, it has FOR EACH ROW to act
on every single row. For example, if my
table has 10 rows, and I issue the statement, DELETE FROM a1, it will add 10
rows to a1_result table with second column set to D.
These are kinds of funcationality that SQL SERVER don't support and need to
do work to programming to get around it.

If  I convert the program to SQL SERVER like this:

    CREATE TRIGGER trig_A1
        ON a1
        AFTER INSERT,UPDATE,DELETE
AS
        DECLARE
       AS DECLARE



       FROM Inserted

       FROM Deleted

     IF (select COUNT(*) from Deleted) = 0  -- inserting
       BEGIN
       INSERT INTO a1_result

       END
    ELSE
               IF (SELECT COUNT(*) from INSERTED) = 0  -- Deleting
                     BEGIN
                     INSERT INTO a1_result

                    END
           ELSE                                                    --
updating
                 BEGIN
                     INSERT INTO a1_result

                    END

This program work if I update or deleting row one by one with each command.
If I issue
"UPDATE a1 SET col2 = 99, it will update each row in a1 table but only
insert one row to a1_result
with the last row of a1 with c2='U'.Also, DELETE FROM a1 will delete all
rows in a1 but only one row is inserted
into a1_result which is last row of a1 with c2='D'.
So I change my program to use cursor like this:

    CREATE TRIGGER trig_a1
       ON a1
        AFTER INSERT,UPDATE,DELETE
AS
        DECLARE


     DECLARE insCursor CURSOR FOR

         SELECT col1, col2, col3
           FROM Inserted
    DECLARE delCursor CURSOR  FOR

          SELECT col1, col2, col3
            FROM Deleted
IF (select COUNT(*) from DELETED) = 0  -- INSERTING
   BEGIN
   OPEN insCursor
   FETCH NEXT FROM insCursor


       BEGIN
       INSERT INTO A1_result

       FETCH NEXT FROM insCursor

       END
    END
ELSE
   IF (SELECT COUNT(*) from INSERTED) = 0
     BEGIN
     OPEN delCursor
     FETCH NEXT FROM delCursor


             BEGIN
             INSERT INTO A1_result

             FETCH NEXT FROM delCursor

          END
     END
   ELSE
         BEGIN
         OPEN delCursor
         FETCH NEXT FROM delCursor


             BEGIN
             INSERT INTO A1_result

              FETCH NEXT FROM delCursor

              END
     END
 CLOSE insCursor
 DEALLOCATE insCursor
 CLOSE delCursor
 DEALLOCATE delCursor

When I issue "UPDATE a1 SET col2=99" and "DELETE FROM a1"
in two seperate SQL session, they both work fine. I can see 10 rows got
inserted with
c2="U" and 10 row got inserted with c2="D" in table a1_result. However, if I
run the first update statement
follow by the delete statemet, I got the following error:

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Server: Msg 16917, Level 16, State 1, Procedure Trig_a1, Line 57
Cursor is not open.
The statement has been terminated.

I have created a procedure to get this error:
    CREATE  procedure PRO_A1
    AS
    BEGIN
    update A1 SET col2 = 00
    delete FROM A1
    end


help?



Wed, 15 Sep 2004 02:21:38 GMT
 Cursor in Trigger - with cursor not open when running second time in the same SQL session


Quote:
> I am converting a Oracle Trigger to SQL SERVER trigger and have probelm.
> The Oracle program looks like this:

>     TABLE a1:                                                Table
> a1_result:
>             col1        INT                                        c1
INT
>             col2        INT                                        c2
> VARCHAR(10)

>     CREATE TRIGGER  trig_A1
>         AFTER INSERT OR UPDATE OR DELETE ON a1
>         REFERENCEING
>             NEW AS NEW
>             OLD AS OLD
>       FOR EACH ROW
>      BEGIN
>      IF INSERTING THEN            -- when inserting
>              INSERT INTO a1_result VALUES (:new.col1,'I')
>      ELSIF UPDATING THEN     -- when updating
>             INSERT INTO a1_result VALUES (:new.col1,'U')
>     ELSE                                        --  when deleting
>             INSERT INTO a1_result VALUES (:new.col1,'D')
>    ENDIF
>    END

> In oracle, it has resever word "INSERTING", UPDATING and DELETING to check
> the status of
> what condition cause the trigger to fire. Also, it has FOR EACH ROW to act
> on every single row. For example, if my
> table has 10 rows, and I issue the statement, DELETE FROM a1, it will add
10
> rows to a1_result table with second column set to D.
> These are kinds of funcationality that SQL SERVER don't support and need
to
> do work to programming to get around it.

> If  I convert the program to SQL SERVER like this:

>     CREATE TRIGGER trig_A1
>         ON a1
>         AFTER INSERT,UPDATE,DELETE
> AS
>         DECLARE
>        AS DECLARE



>        FROM Inserted

>        FROM Deleted

>      IF (select COUNT(*) from Deleted) = 0  -- inserting
>        BEGIN
>        INSERT INTO a1_result

>        END
>     ELSE
>                IF (SELECT COUNT(*) from INSERTED) = 0  -- Deleting
>                      BEGIN
>                      INSERT INTO a1_result

>                     END
>            ELSE                                                    --
> updating
>                  BEGIN
>                      INSERT INTO a1_result

>                     END


use them afterwards. Anyway,

     CREATE TRIGGER trig_A1
         ON a1
         AFTER INSERT,UPDATE,DELETE
 AS
      IF (select COUNT(*) from Deleted) = 0  -- inserting
        INSERT INTO a1_result SELECT col1, 'I' FROM Inserted
     ELSE
                IF (SELECT COUNT(*) from INSERTED) = 0  -- Deleting
                    INSERT INTO a1_result SELECT col1, 'D' FROM Deleted
                ELSE -- updating
                    INSERT INTO a1_result SELECT col1, 'U' FROM Deleted

- Show quoted text -

Quote:

> This program work if I update or deleting row one by one with each
command.
> If I issue
> "UPDATE a1 SET col2 = 99, it will update each row in a1 table but only
> insert one row to a1_result
> with the last row of a1 with c2='U'.Also, DELETE FROM a1 will delete all
> rows in a1 but only one row is inserted
> into a1_result which is last row of a1 with c2='D'.
> So I change my program to use cursor like this:

>     CREATE TRIGGER trig_a1
>        ON a1
>         AFTER INSERT,UPDATE,DELETE
> AS
>         DECLARE


>      DECLARE insCursor CURSOR FOR

>          SELECT col1, col2, col3
>            FROM Inserted
>     DECLARE delCursor CURSOR  FOR

>           SELECT col1, col2, col3
>             FROM Deleted
> IF (select COUNT(*) from DELETED) = 0  -- INSERTING
>    BEGIN
>    OPEN insCursor
>    FETCH NEXT FROM insCursor


>        BEGIN
>        INSERT INTO A1_result

>        FETCH NEXT FROM insCursor

>        END
>     END
> ELSE
>    IF (SELECT COUNT(*) from INSERTED) = 0
>      BEGIN
>      OPEN delCursor
>      FETCH NEXT FROM delCursor


>              BEGIN
>              INSERT INTO A1_result

>              FETCH NEXT FROM delCursor

>           END
>      END
>    ELSE
>          BEGIN
>          OPEN delCursor
>          FETCH NEXT FROM delCursor


>              BEGIN
>              INSERT INTO A1_result

>               FETCH NEXT FROM delCursor

>               END
>      END
>  CLOSE insCursor
>  DEALLOCATE insCursor
>  CLOSE delCursor
>  DEALLOCATE delCursor

> When I issue "UPDATE a1 SET col2=99" and "DELETE FROM a1"
> in two seperate SQL session, they both work fine. I can see 10 rows got
> inserted with
> c2="U" and 10 row got inserted with c2="D" in table a1_result. However, if
I
> run the first update statement
> follow by the delete statemet, I got the following error:

> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)
> (1 row(s) affected)

> Server: Msg 16917, Level 16, State 1, Procedure Trig_a1, Line 57
> Cursor is not open.
> The statement has been terminated.

> I have created a procedure to get this error:
>     CREATE  procedure PRO_A1
>     AS
>     BEGIN
>     update A1 SET col2 = 00
>     delete FROM A1
>     end


> help?



Wed, 15 Sep 2004 02:48:23 GMT
 Cursor in Trigger - with cursor not open when running second time in the same SQL session

Quote:

>     CREATE TRIGGER trig_A1
>         ON a1
>         AFTER INSERT,UPDATE,DELETE
> AS
>      IF (select COUNT(*) from Deleted) = 0  -- inserting

I think even better:

        IF NOT EXISTS (SELECT TOP 1 'x' from DELETED)

Quote:
>        INSERT INTO a1_result SELECT col1, 'I' FROM Inserted
>     ELSE
>                IF (SELECT COUNT(*) from INSERTED) = 0  -- Deleting

Better:

                        IF NOT EXISTS (SELECT TOP 1 'x' from INSERTED)

Quote:
>                    INSERT INTO a1_result SELECT col1, 'D' FROM Deleted
>                ELSE -- updating
>                    INSERT INTO a1_result SELECT col1, 'U' FROM Deleted

>> This program work if I update or deleting row one by one with each
>command.
>> If I issue
>> "UPDATE a1 SET col2 = 99, it will update each row in a1 table but only
>> insert one row to a1_result
>> with the last row of a1 with c2='U'.Also, DELETE FROM a1 will delete all
>> rows in a1 but only one row is inserted
>> into a1_result which is last row of a1 with c2='D'.
>> So I change my program to use cursor like this:

>>     CREATE TRIGGER trig_a1
>>        ON a1
>>         AFTER INSERT,UPDATE,DELETE
>> AS
>>         DECLARE


>>      DECLARE insCursor CURSOR FOR

>>          SELECT col1, col2, col3
>>            FROM Inserted
>>     DECLARE delCursor CURSOR  FOR

>>           SELECT col1, col2, col3
>>             FROM Deleted
>> IF (select COUNT(*) from DELETED) = 0  -- INSERTING
>>    BEGIN
>>    OPEN insCursor
>>    FETCH NEXT FROM insCursor


>>        BEGIN
>>        INSERT INTO A1_result

>>        FETCH NEXT FROM insCursor

>>        END
>>     END
>> ELSE
>>    IF (SELECT COUNT(*) from INSERTED) = 0
>>      BEGIN
>>      OPEN delCursor
>>      FETCH NEXT FROM delCursor


>>              BEGIN
>>              INSERT INTO A1_result

>>              FETCH NEXT FROM delCursor

>>           END
>>      END
>>    ELSE
>>          BEGIN
>>          OPEN delCursor
>>          FETCH NEXT FROM delCursor


>>              BEGIN
>>              INSERT INTO A1_result

>>               FETCH NEXT FROM delCursor

>>               END
>>      END
>>  CLOSE insCursor
>>  DEALLOCATE insCursor
>>  CLOSE delCursor
>>  DEALLOCATE delCursor

>> When I issue "UPDATE a1 SET col2=99" and "DELETE FROM a1"
>> in two seperate SQL session, they both work fine. I can see 10 rows got
>> inserted with
>> c2="U" and 10 row got inserted with c2="D" in table a1_result. However, if
>I
>> run the first update statement
>> follow by the delete statemet, I got the following error:

>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)
>> (1 row(s) affected)

>> Server: Msg 16917, Level 16, State 1, Procedure Trig_a1, Line 57
>> Cursor is not open.
>> The statement has been terminated.

>> I have created a procedure to get this error:
>>     CREATE  procedure PRO_A1
>>     AS
>>     BEGIN
>>     update A1 SET col2 = 00
>>     delete FROM A1
>>     end


>> help?

--
Harlan Messinger
Remove the first dot from my e-mail address.
Veuillez ?ter le premier point de mon adresse de courriel.


Wed, 15 Sep 2004 11:22:52 GMT
 Cursor in Trigger - with cursor not open when running second time in the same SQL session
    You don't need to do row-by-row operation really. It is efficient to do
set-based approach especially in triggers.

CREATE TRIGGER  trig_A1
        AFTER INSERT OR UPDATE OR DELETE ON a1
        REFERENCEING
            NEW AS NEW
            OLD AS OLD
      FOR EACH ROW
     BEGIN
     IF INSERTING THEN            -- when inserting
             INSERT INTO a1_result VALUES (:new.col1,'I')
     ELSIF UPDATING THEN     -- when updating
            INSERT INTO a1_result VALUES (:new.col1,'U')
    ELSE                                        --  when deleting
            INSERT INTO a1_result VALUES (:new.col1,'D')
   ENDIF
   END

can be written simply as:

CREATE TRIGGER trig_A1
AFTER INSERT, UPDATE, DELETE
ON a1
BEGIN
    if exists( select * from inserted )
        insert into a_result
        select i.col1,
               case when exists( select * from deleted )
                    then 'U'
                    else 'I'
               end
          from inserted
    else
        insert into a_result
        select i.col1, 'D'
          from inserted
END

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Thu, 16 Sep 2004 07:22:20 GMT
 Cursor in Trigger - with cursor not open when running second time in the same SQL session
Is there any reason you are using cursors?  It looks like what you are
doing is creating a history table whenever an insert, update or delete
occurs, why not do an insert -select into A1_result from inserted or
deleted.

Also creating 3 separate triggers will eliminate the need to check the
contents of inserted and deleted to determine what action is occurring

Create Trigger trg_a1_insert on A1
for Insert
As
Insert A1_result
select Col1, 'I' from Inserted

Create Trigger trg_a1_update on a1
for update
as
Insert A1_result
select col1, 'U' from inserted

Create Trigger trg_a1_delete on a1
for delete
as
Insert  A1_result
Select col1, 'D' from deleted

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Fri, 17 Sep 2004 22:34:17 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Multiple cursors - second cursor hangs when opened

2. Why declare cursor and open cursor both cost same amount of time

3. Need Help Please , Invalid Cursor Position Error and Cursor Not Open Error

4. How to obtain number of opened cursors from any opened session

5. Help ! Opened cursors exceed max opened cursors

6. Open cursors and more open cursors

7. forms 5 - cursor problem (Not PL/SQL cursor)

8. problems using cursors in SP's (6.5) - second time

9. Cursors remain opened- until reach server resource limit then session crash down

10. Sniped sessions and its affects (open cursors)

11. Detect if Cursor Opened and Detect if Cursor Allocated

12. 40088 No open cursor or cursor closed.


 
Powered by phpBB® Forum Software