Professional Web Applications Themes

Update trigger problem - Microsoft SQL / MS SQL Server

Greetings, I am trying to do send a mail message when a certain field gets updated in my table (trying to track when this is happening and by who) . Tho when i look at the inserted table i get there are no records there. Table and trigger below also the query I run from query yzer to trigger it. My problem seems to be i get no values in my inserted table, as i have tried to print the values out and got nothing. I only want the trigger to send emails when the Receipts_Rec field is updated. Any ...

  1. #1

    Default Update trigger problem

    Greetings,
    I am trying to do send a mail message when a certain field gets updated
    in my table (trying to track when this is happening and by who) . Tho when
    i look at the inserted table i get there are no records there. Table and
    trigger below also the query I run from query yzer to trigger it. My
    problem seems to be i get no values in my inserted table, as i have tried to
    print the values out and got nothing. I only want the trigger to send
    emails when the Receipts_Rec field is updated. Any suggestions to what im
    doing wrong is apprecaited.

    TIA
    Stephen F Zelonis
    [email]steve.zeloniswestonsolutions.com[/email]
    If you feel a tingle, that's probably your DNA changing as you read this...
    ----------------------------------------------------------------------------
    -----------
    Table
    ----------------------------------------------------------------------------
    -----------

    CREATE TABLE [dbo].[ET_Hdr] (
    [ET_Hdr_Id] [int] IDENTITY (34000, 1) NOT NULL ,
    [ET_Type_Id] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Hdr_Date] [datetime] NOT NULL ,
    [Empl_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Purpose] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Method_Id] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Entered_By_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [Status_Id] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Status_Date] [datetime] NOT NULL ,
    [Approver_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [Departed_Date] [datetime] NULL ,
    [Time_Departed] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Returned_Date] [datetime] NULL ,
    [Time_Returned] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Total_Amt] [numeric](9, 2) NULL ,
    [Tot_Out_Of_Pocket] [numeric](9, 2) NULL ,
    [Modified_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [Timestamp] [datetime] NOT NULL ,
    [Odometer_Beg] [numeric](8, 0) NULL ,
    [Odometer_End] [numeric](8, 0) NULL ,
    [Total_Miles] [numeric](8, 0) NULL ,
    [Bus_Miles] [numeric](8, 0) NULL ,
    [Personal_Miles] [numeric](8, 0) NULL ,
    [Paid_Fl] [int] NOT NULL ,
    [Paid_Date] [datetime] NULL ,
    [Receipts_Req] [int] NOT NULL ,
    [Receipts_Rec] [int] NOT NULL
    ) ON [ET_DATA]

    -------------------------------------------------------------------------
    Trigger
    -------------------------------------------------------------------------
    Create trigger Email_on_Receipts_Rec
    on ET_HDR
    FOR update
    as

    if UPDATE(Receipts_Rec) and (select count(*) from inserted) > 0

    Begin

    Declare Flag as int
    Declare mod as Varchar(25)
    Declare time as datetime
    Declare hdr as int
    Declare Subject1 as Varchar(50)
    declare Q as varchar(100)

    DECLARE ErrNum integer
    DECLARE ErrSrc varchar(256)
    DECLARE ErrDesc varchar(256)
    DECLARE ErrProc varchar(256)

    DECLARE Subject varchar(200)
    DECLARE Body1 varchar(8000)
    DECLARE Template varchar(8000)
    DECLARE BodyFormat int
    DECLARE MailFormat int
    DECLARE Priority int
    DECLARE MailCount integer

    Declare FSName varchar(256)
    Declare MailTemplatesFolder varchar(256)
    Declare LogoPath varchar(256)
    DECLARE FileSpec varchar(256)


    Declare Receipts_CUR CURSOR FOR
    Select ET_HDR_ID ,Receipts_REC, modified_by, [timestamp] from inserted
    OPEN Receipts_CUR

    FETCH NEXT FROM Receipts_CUR into hdr, FLAG,MOD,TIME

    Set body1 = 'Header ID=' + Cast(HDR as varchar(25)) + CHAR(13) + ' Flag
    value =' + Cast(Flag as Varchar(10)) + CHAR(13) + ' Modified by = ' + mod
    + CHAR(13) + 'Time= ' + Cast(time as Varchar(20))
    set Subject1 = 'Receipts Trigger Email for ' + cast(hdr as varchar(20))
    set Q ='SELECT ET_HDR_ID,TIMESTAMP,MODIFIED_BY FROM ET_HDR where ET_HDR_ID
    =' + cast(hdr as varchar(20))


    EXEC master.dbo.xp_sendmail recipients = 'Zelonis, Stephen F.',
    message = Body1 ,
    query = Q ,
    attachments ='',
    copy_recipients = 'Bowen, Clay',
    blind_copy_recipients ='' ,
    subject = Subject1


    Close Receipts_CUR
    DEALLOCATE Receipts_CUR
    End
    -------------------------------------------------------------
    Queries (alternate running these to always change my value)
    -------------------------------------------------------------
    update ET_HDR Set Receipts_Rec =-1 where et_HDR_ID = 41309
    update ET_HDR Set Receipts_Rec =0 where et_HDR_ID = 41309


    Stephen F Zelonis Guest

  2. #2

    Default Re: Update trigger problem

    This "update ET_HDR Set Receipts_Rec =-1 where et_HDR_ID = 41309" query does
    nothing more than updating Receipts_Rec column which forces the
    "UPDATE(Receipts_Rec)" to return true. Because your "update" above does not
    supply [Modified_By] the inserted table would not have such info for your to
    assign to mod.

    Simply change your trigger to the below and see what you get.

    Create trigger Email_on_Receipts_Rec
    on ET_HDR
    FOR update
    as

    if UPDATE(Receipts_Rec)
    select * from inserted
    GO


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "Stephen F Zelonis" <steve.zeloniswestonsolutions.com> wrote in message
    news:eOcPW2NRDHA.2204TK2MSFTNGP12.phx.gbl...
    > Greetings,
    > I am trying to do send a mail message when a certain field gets
    updated
    > in my table (trying to track when this is happening and by who) . Tho
    when
    > i look at the inserted table i get there are no records there. Table and
    > trigger below also the query I run from query yzer to trigger it. My
    > problem seems to be i get no values in my inserted table, as i have tried
    to
    > print the values out and got nothing. I only want the trigger to send
    > emails when the Receipts_Rec field is updated. Any suggestions to what im
    > doing wrong is apprecaited.
    >
    > TIA
    > Stephen F Zelonis
    > [email]steve.zeloniswestonsolutions.com[/email]
    > If you feel a tingle, that's probably your DNA changing as you read
    this...
    > --------------------------------------------------------------------------
    --
    > -----------
    > Table
    > --------------------------------------------------------------------------
    --
    > -----------
    >
    > CREATE TABLE [dbo].[ET_Hdr] (
    > [ET_Hdr_Id] [int] IDENTITY (34000, 1) NOT NULL ,
    > [ET_Type_Id] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    > [Hdr_Date] [datetime] NOT NULL ,
    > [Empl_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > [Purpose] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [Method_Id] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [Entered_By_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    > NULL ,
    > [Status_Id] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [Status_Date] [datetime] NOT NULL ,
    > [Approver_Id] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    > ,
    > [Departed_Date] [datetime] NULL ,
    > [Time_Departed] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [Returned_Date] [datetime] NULL ,
    > [Time_Returned] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [Total_Amt] [numeric](9, 2) NULL ,
    > [Tot_Out_Of_Pocket] [numeric](9, 2) NULL ,
    > [Modified_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    > ,
    > [Timestamp] [datetime] NOT NULL ,
    > [Odometer_Beg] [numeric](8, 0) NULL ,
    > [Odometer_End] [numeric](8, 0) NULL ,
    > [Total_Miles] [numeric](8, 0) NULL ,
    > [Bus_Miles] [numeric](8, 0) NULL ,
    > [Personal_Miles] [numeric](8, 0) NULL ,
    > [Paid_Fl] [int] NOT NULL ,
    > [Paid_Date] [datetime] NULL ,
    > [Receipts_Req] [int] NOT NULL ,
    > [Receipts_Rec] [int] NOT NULL
    > ) ON [ET_DATA]
    >
    > -------------------------------------------------------------------------
    > Trigger
    > -------------------------------------------------------------------------
    > Create trigger Email_on_Receipts_Rec
    > on ET_HDR
    > FOR update
    > as
    >
    > if UPDATE(Receipts_Rec) and (select count(*) from inserted) > 0
    >
    > Begin
    >
    > Declare Flag as int
    > Declare mod as Varchar(25)
    > Declare time as datetime
    > Declare hdr as int
    > Declare Subject1 as Varchar(50)
    > declare Q as varchar(100)
    >
    > DECLARE ErrNum integer
    > DECLARE ErrSrc varchar(256)
    > DECLARE ErrDesc varchar(256)
    > DECLARE ErrProc varchar(256)
    >
    > DECLARE Subject varchar(200)
    > DECLARE Body1 varchar(8000)
    > DECLARE Template varchar(8000)
    > DECLARE BodyFormat int
    > DECLARE MailFormat int
    > DECLARE Priority int
    > DECLARE MailCount integer
    >
    > Declare FSName varchar(256)
    > Declare MailTemplatesFolder varchar(256)
    > Declare LogoPath varchar(256)
    > DECLARE FileSpec varchar(256)
    >
    >
    > Declare Receipts_CUR CURSOR FOR
    > Select ET_HDR_ID ,Receipts_REC, modified_by, [timestamp] from inserted
    > OPEN Receipts_CUR
    >
    > FETCH NEXT FROM Receipts_CUR into hdr, FLAG,MOD,TIME
    >
    > Set body1 = 'Header ID=' + Cast(HDR as varchar(25)) + CHAR(13) + ' Flag
    > value =' + Cast(Flag as Varchar(10)) + CHAR(13) + ' Modified by = ' +
    mod
    > + CHAR(13) + 'Time= ' + Cast(time as Varchar(20))
    > set Subject1 = 'Receipts Trigger Email for ' + cast(hdr as varchar(20))
    > set Q ='SELECT ET_HDR_ID,TIMESTAMP,MODIFIED_BY FROM ET_HDR where
    ET_HDR_ID
    > =' + cast(hdr as varchar(20))
    >
    >
    > EXEC master.dbo.xp_sendmail recipients = 'Zelonis, Stephen F.',
    > message = Body1 ,
    > query = Q ,
    > attachments ='',
    > copy_recipients = 'Bowen, Clay',
    > blind_copy_recipients ='' ,
    > subject = Subject1
    >
    >
    > Close Receipts_CUR
    > DEALLOCATE Receipts_CUR
    > End
    > -------------------------------------------------------------
    > Queries (alternate running these to always change my value)
    > -------------------------------------------------------------
    > update ET_HDR Set Receipts_Rec =-1 where et_HDR_ID = 41309
    > update ET_HDR Set Receipts_Rec =0 where et_HDR_ID = 41309
    >
    >

    oj Guest

  3. #3

    Default Re: Update trigger problem

    Thanks, but this still doesnt help as I still am seeing no records in the
    inserted table. Also yes I know those queries do not touch modified_by, but
    they were just test queries to see if the trigger was working, my actual VB
    code that will change Receipts_Rec will also modify that field when it runs.

    Stephen F Zelonis
    [email]steve.zeloniswestonsolutions.com[/email]
    If you feel a tingle, that's probably your DNA changing as you read

    "oj" <nospam_ojngohome.com> wrote in message
    news:#KGMRnQRDHA.2020TK2MSFTNGP11.phx.gbl...
    > This "update ET_HDR Set Receipts_Rec =-1 where et_HDR_ID = 41309" query
    does
    > nothing more than updating Receipts_Rec column which forces the
    > "UPDATE(Receipts_Rec)" to return true. Because your "update" above does
    not
    > supply [Modified_By] the inserted table would not have such info for your
    to
    > assign to mod.
    >
    > Simply change your trigger to the below and see what you get.
    >
    > Create trigger Email_on_Receipts_Rec
    > on ET_HDR
    > FOR update
    > as
    >
    > if UPDATE(Receipts_Rec)
    > select * from inserted
    > GO
    >
    >
    > --
    > -oj
    > RAC v2.2 & QALite!
    > [url]http://www.rac4sql.net[/url]

    Stephen F Zelonis Guest

Similar Threads

  1. Allowing update of column only from trigger
    By Shawn Harrison in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 31st, 06:59 PM
  2. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  3. SQL2K Trigger problem
    By Joe Bertolini in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 2nd, 08:42 AM
  4. Trigger to update another data source
    By Mandar Naik[MSFT] in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 09:20 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139