Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Can't reference logical table within cursor

    Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

    DECLARE check_contact_fields CURSOR
    FOR SELECT field_id, column_name FROM contacts_fields
    OPEN check_contact_fields
    FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted'
    exec sp_executesql @SQL
    set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted'
    exec sp_executesql @SQL
    if (select * from ##DeletedData) <> (select * from ##InsertedData)
    select * from ##InsertedData
    FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
    END
    CLOSE check_contact_fields
    DEALLOCATE check_contact_fields

    drop table ##DeletedData
    drop table ##InsertedData


    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'deleted'.


    TIA

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

    Thanks again

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe you can tell us what you are trying to do...I can't figure it out...

    AND a cursor in a trigger is never a good idea.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2003
    Posts
    97
    Brett -

    I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

    Thanks

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why bother...just update the whole row....what difference does it make...

    What's the DDL of the table look like?

    What do you mean by dynamic?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2003
    Posts
    97
    DDL ("master" audit table):
    /****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
    CREATE TABLE [contacts_history] (
    [contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
    [contact_id] [int] NULL ,
    [who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
    (
    [contact_history_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    DDL (data capture table):
    /****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
    CREATE TABLE [contacts_history_values] (
    [contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [contact_history_id] [int] NULL ,
    [field_id] [int] NULL ,
    [old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
    (
    [contacts_history_value_ID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

    Thanks

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    holy crap...

    You basically have "vertical" rows.....

    Is this a third party product, did you build this or did you inherit this

    What does the concat table DDL look like?

    And what do you mean the "fields" (you mean columns right) can change on the fly.

    Do you mean the data or the table structure?

    What does "if any of the column_name values have changed" mean?

    I'm sorry...I just can't see it yet....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2003
    Posts
    97
    Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

    I mean the table structure.

    What does "if any of the column_name values have changed" mean?
    ---> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

    Hope this makes sense.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ok...I understand...not that I want to......

    Is the id 1 per "table"

    Are you making up and configuring "tables" for end users through a web application?

    I'd be very interested in the reason that your doing this....

    As for your solution...I gotta ponder this one......

    Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

    It doesn't work that way.

    You column name is essentially a key in this process...actually a composite key along with contactId

    So actually trying to udate the key...which to me is a bad thing...

    You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

    That should be rather straight forward in a trigger....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Mar 2003
    Posts
    97
    Sorry, but I don't follow you.

    Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
    This allows the app to keep track of the column that was modified.

    I think I'm more confused now... =\

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok good....

    I think I got confused...

    You want to audit the change correct?

    Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

    What happens for a new "field" or a field that has been "deleted"

    You still haven't told me who built this....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Mar 2003
    Posts
    97
    I think I found the right design but I'm having issues executing it.

    DECLARE check_contact_fields CURSOR LOCAL
    FOR SELECT field_id, column_name FROM contacts_fields
    OPEN check_contact_fields
    FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    if (select '+@column_name+' from inserted) <> (select '+@column_name+' from deleted)
    print @column_name + ' boo'
    else if (select '+@column_name+' from inserted) = (select '+@column_name+' from deleted)
    print 'identical values'
    FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
    END
    CLOSE check_contact_fields
    DEALLOCATE check_contact_fields

    This always returns "identical values" even though some of the data has changed.

    Any ideas?
    Last edited by naceBal; 08-05-04 at 10:30.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What table is the Trigger on?

    I don't think you want or need the cursor
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I Think you want this...can't test without the other tables DDL

    Code:
    CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
    AS
      BEGIN
    	INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
    	SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
    	  FROM inserted i INNER JOIN deleted o
    	 WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
      END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •