Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: SQL 2005 trigger help

    Hi,

    I am very new to the 'trigger' concept and need to set an update trigger on table table alerts to update the alertcount field in table alertcounter:

    here is what I have s far:

    USE [stock]
    GO
    /****** Object: Trigger [dbo].[trg_increment Alert] Script Date: 10/21/2008 11:39:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER trigger [dbo].[trg_increment alert] on [dbo].[TblAlerts] After Update
    as
    begin

    update tblalertcounter set alertcount = alertcount+1


    end

    I get no errors and the alertcount field does not increment by 1.

    Please let me know where I am going wrong.

    Cheers,

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many rows currently in tblalertcounte?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Smile

    Hi,

    Thanks for the reply.

    There is 1 record which I inserted manually

    ID ALERTCOUNT
    ======================
    1 0


    ID data type is int and is identity - identity increment

    alertcount data type is numeric(18,0)

    Cheers!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post an example of the SQL you are executing on tblAlterts?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2003
    Posts
    58

    Red face

    Hi,

    I am not sure supplying the data that appears in tblalerts will help. I think I am explaining my intentions incorrectly. Anyway, TBLAlerts has the following fields:
    AlertId,ID,Category,Item,Description,qty,AlertDate

    When data is inserted into TBLalerts, I basically want some way of creating an alert in my ASP2 webpage. I am using VS 2005 ASP2/VB. I have a label on my webform which should display the number of alerts.

    When data is inserted into TblAlerts I thought I could track this by using a trigger. for example:

    Data is inserted into tblalerts
    trigger is initiated and updates alertcount in tblalertcounter by 1

    I then plan to open a connection (from my web app using VB code) to tblalertcounter and store the contents of alertcount in LblAlert.text.

    So, in theory there should only ever be one record in tblalertcounter and the value in alertcount will simple increment by 1 each time data is inserted in TblAlerts.

    There probably is abetter way of doing this, I am just inexperienced and I beginning to confuse myself! LOL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could just insert a new record per alert and then the number of alerts is
    Code:
    SELECT Count(*)
    FROM   alerts
    Simple
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2003
    Posts
    58
    LOL
    I cannot believe it - fantastic!

    Cheers!!!!!!!!!!!!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by neoice
    I am not sure supplying the data that appears in tblalerts will help.
    Never said it would - I wanted to see the statement.

    Quote Originally Posted by neoice
    When data is inserted into TBLalerts
    ...and that's why I wanted to see the code - your trigger is an after UPDATE trigger. Update <> Insert
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2003
    Posts
    58
    The trigger was actually on tblalerts and updating the field 'alertcount' in tblalertcounter :O) - I actually got this to work in the end but George came up with a much smarter solution!

    Live and learn I say :O)

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by neoice
    The trigger was actually on tblalerts and updating the field 'alertcount' in tblalertcounter :O) - I actually got this to work in the end but George came up with a much smarter solution!

    Live and learn I say :O)
    You are right - George's solution is much better. I am just trying to explain why the trigger didn't work. Do you understand now why your trigger didn't fire?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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