Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: Problem in Trigger

    Hi Guyz
    I am newbee in this sql server.
    I am trying to create a trigger. Check is successful but when i execute update command i get following error.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type nvarchar

    Here is my trigger
    CREATE TRIGGER Update_Total ON EstItems
    AFTER UPDATE
    AS
    Declare @SRTotal money
    Declare @GTot nvarchar
    Declare @CI money
    Declare @Cont money
    Declare @Dept money
    Declare @CIndex nvarchar
    Declare @ContV nvarchar
    Declare @TotCI nvarchar
    Declare @TotCont nvarchar
    Declare @DeptV nvarchar
    Declare @TotDept nvarchar

    SET @CI = (SELECT CostIndxPerc FROM Title)
    SET @Cont = (SELECT ContPerc FROM Title)
    SET @Dept = (SELECT DeptPerc FROM Title)
    SET @SRTotal = (SELECT SUM (isnull(OriginalAmount, 0)) FROM EstItems WHERE (SRItem = '1'))
    SET @GTot = (SELECT SUM(isnull(OriginalAmount, 0)) FROM EstItems)
    SET @CIndex = ((cast (@GTot as float) * cast (@CI as float)) / 100)
    SET @ContV = ((cast (@GTot as float) * cast (@Cont as float)) / 100)
    SET @DeptV = ((cast (@GTot as float) * cast (@Dept as float)) / 100)
    SET @TotCI = (cast (@GTot as float) + cast (@CIndex as float))
    SET @TotCont = (cast (@GTot as float) + cast (@ContV as float))
    SET @TotDept = (cast (@GTot as float) + cast (@DeptV as float))

    UPDATE Title
    SET SRValue = @SRTotal,
    GTotal = @GTot,
    CostIndxVal = @CIndex,
    TotAftCI = @TotCI,
    Contval = @ContV,
    TotAftCont = @TotCont,
    DeptVal = @DeptV,
    TotAftDept = @TotDept


    And here is my tables.
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EstItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[EstItems]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Title]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Title]
    GO

    CREATE TABLE [dbo].[EstItems] (
    [ItemId] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [EBDescr] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EFDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EUnit] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HBDescr] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HFDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HUnit] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NUnit] [int] NULL ,
    [NAUnit] [float] NULL ,
    [Analysis] [bit] NOT NULL ,
    [GroupId] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GorI] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Special] [bit] NOT NULL ,
    [SortId] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Cement] [float] NULL ,
    [Wcement] [float] NULL ,
    [Bitumen] [float] NULL ,
    [Qty] [money] NULL ,
    [PQty] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MeasurYN] [bit] NOT NULL ,
    [OriginalPrice] [money] NULL ,
    [OriginalAmount] [float] NULL ,
    [OrgAmtText] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [JustPrice] [money] NULL ,
    [JustAmount] [float] NULL ,
    [QuotPrice] [money] NULL ,
    [QuotAmount] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AgreedPrice] [money] NULL ,
    [AgreedAmount] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GroupNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GroupSLNo] [int] NULL ,
    [TenderRate] [money] NULL ,
    [AhrAlr] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TenderAmount] [float] NULL ,
    [AgrAmount] [money] NULL ,
    [SLNo] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Substd] [bit] NOT NULL ,
    [AhrAlrJustRate] [money] NULL ,
    [SRItem] [bit] NOT NULL ,
    [AhrAlrJustAmount] [money] NULL ,
    [L1Rate] [money] NULL ,
    [L1Amount] [money] NULL ,
    [R1] [money] NULL ,
    [R2] [money] NULL ,
    [R3] [money] NULL ,
    [R4] [money] NULL ,
    [R5] [money] NULL ,
    [R10] [money] NULL ,
    [R20] [money] NULL ,
    [R21] [money] NULL ,
    [R500] [money] NULL ,
    [R550] [money] NULL ,
    [Lead] [money] NULL ,
    [LeadUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LeadType] [smallint] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Title] (
    [BName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FName] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SRValue] [money] NULL ,
    [GTotal] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CostIndxOn] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CostIndxPerc] [money] NULL ,
    [CostIndxVal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TotAftCI] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ContPerc] [money] NULL ,
    [ContVal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TotAftCont] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeptPerc] [money] NULL ,
    [DeptVal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TotAftDept] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Duration] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TSNo] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AgreeNo] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DiscPerc] [money] NULL ,
    [DiscVal] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TenderNo] [int] NULL ,
    [Tenderer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ItemOrder] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Hindi] [int] NULL ,
    [SHead] [bit] NOT NULL ,
    [PrintOR] [bit] NOT NULL ,
    [CostFactor] [money] NULL ,
    [CivTot] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CovTot] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Gtv] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StrCIndex] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [strCond] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StrDC] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StrWords] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NITGTotal] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [L1Tender] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [L1TenderName] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EstimteNo] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AAESNo] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AAESAmt] [float] NULL ,
    [AAESHA] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BudgtConNo] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BudgtConAmt] [float] NULL ,
    [BudgtConHA] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BillDeviation] [float] NULL ,
    [SDPercent] [float] NULL ,
    [STPercent] [float] NULL ,
    [ITPercent] [float] NULL ,
    [SCPercent] [float] NULL ,
    [CessPercent] [float] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First up, what would happen if you updated more than one record at once?

    Also, if your variables are declared as nvarchar, then you won't be able to "shove" an numeric values in to them. Instead, you will have to Cast()/Convert() them.
    George
    Home | Blog

Posting Permissions

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