Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Instead of Delete Trigger

    i have created a view from two tables in my database (ProjectedOutputs, and Output)

    since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions

    CREATE TRIGGER Test
    ON V_ProjectOutputs
    INSTEAD OF DELETE
    AS

    DELETE PROJECTEDOUTPUTS
    FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    post the view definition please
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    30

    view definition for instead of delete

    SELECT dbo.PROJECTEDOUTPUTS.ProjectedOutputID, dbo.PROJECTEDOUTPUTS.ProjectID, dbo.PROJECTEDOUTPUTS.OutputID,
    dbo.[OUTPUT].OutputHeading, dbo.PROJECTEDOUTPUTS.OutputAmount
    FROM dbo.PROJECTEDOUTPUTS LEFT OUTER JOIN
    dbo.[OUTPUT] ON dbo.PROJECTEDOUTPUTS.OutputID = dbo.[OUTPUT].OutputID

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The records in OUTPUT shouldn't be deleted, instead they are falling outside the scope of your view definition.
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Posts
    30
    can you explain that to me please, i am not sure what you mean

  6. #6
    Join Date
    Oct 2007
    Posts
    30
    Oh actually I think I know what you mean, maybe my trigger isn't working, is there a way i can see whether my trigger is working or not?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm saying that your SQL statement only picks out values from OUTPUT when a corresponding value exists in the PROJECTEDOUTPUTS table (your LEFT JOIN tells me this!).

    Therefore it appears to be deleting both, but in reality the view simply isn't returning them as results!

    To check your delete trigger; delete a record and then see if it still exists in the base tables..?
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2007
    Posts
    30
    hmmm, i think it may be the way i am calling to the view.

    when i do this from vb

    With Me.Adodc_ProjectedOutputs.Recordset
    .UpdateBatch
    End With

    after having deleted it from a datagrid bound to ProjectedOutputs, then it wont work,

    but if i do this in the sql query window
    delete from V_ProjectOutputs where ProjectedOutputid = 10026, then it works.

    do you think I am calling it wrong or something? I do appreciate the help you are giving me

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The test trigger you posted will delete from PROJECTEDOUTPUTS, and only PROJECTEDOUTPUTS.
    Are you sure the data is also being deleted from OUTPUTS? If so, then it is something that your application is doing, or there is possibly a trigger or relationship on the PROJECTEDOUTPUTS table itself which is cascading deletes to OUTPUTS.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2007
    Posts
    30
    for some reason, when i run a delete statement from sql query analyser on my view then the trigger fires ok

    but it i create an adodc on my vb form based on my view and then set this as a datasource for my grid. When i delete records from my grid, then the trigger is not firing, has anyone any idea why?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That conflicts with your previous statement:
    Quote Originally Posted by debbiecoates
    Although the records are deleting OK, so are the records in Output, which i dont want.
    So before we go any farther with this, please take the time to clearly state the issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2007
    Posts
    30
    ah ha, i have found something on microsoft help and support(

    PRB: Trigger Does Not Fire on View When Updated Through ADO ) that says that triggers wont fire unless i use view metadata attirube for a view. It describes the poblem completely, but i cant work out the fix, when i used view metadata, then i get an error msg saying that i cant update my recordset because modification affects multiple base tables

    I feel like i am taking one step forward and three back, any ideas?

  13. #13
    Join Date
    Oct 2007
    Posts
    30
    I've got it

    I changed my view definition to this

    CREATE VIEW V_ProjectOutputs
    WITH VIEW_METADATA
    as
    SELECT
    P.projectedoutputid,
    P.OUTPUTID,
    P.PROJECTID,
    P.OUTPUTAMOUNT,
    O.OUTPUTHEADING
    FROM
    projectedoutputs P,
    [OUTPUT] O
    WHERE P.OUTPUTID = O.OUTPUTID

    and now my trigger is firing, many thanks to those who helped me

    Debbie x

Posting Permissions

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