Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: call store procedure from trigger

    Well I have problem:

    i have a table in databes
    USE

    [graditelj2]
    GO

    /****** Object: Table [dbo].[Stavka_Prijemnice] Script Date: 03/12/2011 09:40:57 ******/

    SET

    ANSI_NULLS ON
    GO

    SET

    QUOTED_IDENTIFIER ON
    GO

    CREATE

    TABLE [dbo].[Stavka_Prijemnice](
    [prijemnica_id] [int]

    NOT NULL,
    [stavka_prijemnice_id] [int]

    NOT NULL,
    [materijal_id] [int]

    NULL,
    [kolicina] [decimal]

    (18, 0) NULL,
    [magacin_id] [int]

    NULL,

    CONSTRAINT [PK_Stavka_Prijemnice_1] PRIMARY KEY CLUSTERED
    (

    [prijemnica_id]

    ASC,
    [stavka_prijemnice_id]

    ASC
    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )

    ON [PRIMARY]
    GO

    i have to every time when i insert row in this table to catch value from column kolicina . In table Stanje_Magacin_StavkaPrijemnice i will have column stanje . In this column i have to add value from kollicina any time i insert new row !



    ............................................. this is table in which i have to input values

    USE

    [graditelj2]
    GO

    /****** Object: Table [dbo].[Stanje_Magacin_StavkaPrijemnice] Script Date: 03/12/2011 10:06:01 ******/

    SET

    ANSI_NULLS ON
    GO

    SET

    QUOTED_IDENTIFIER ON
    GO

    CREATE

    TABLE [dbo].[Stanje_Magacin_StavkaPrijemnice](
    [magacin_id] [int]

    NOT NULL,
    [materijal_id] [int]

    NOT NULL,
    [stanje] [nchar]

    (10) NULL
    )

    ON [PRIMARY]
    GO

    ............................ this is procedure

    USE

    [graditelj2]
    GO

    /****** Object: StoredProcedure [dbo].[sumiraj] Script Date: 03/12/2011 10:06:46 ******/

    SET

    ANSI_NULLS ON
    GO

    SET

    QUOTED_IDENTIFIER ON
    GO

    create

    procedure [dbo].[sumiraj]
    (

    @materijal_id int,
    @kolicina

    decimal OUTPUT)
    AS

    SELECT

    @kolicina= SUM(kolicina)
    from

    Stavka_Prijemnice
    where

    materijal_id = @materijal_id
    return

    GO



    .................

    i try with this triger but it wont work

    CREATE

    TRIGGER UbaciStanjeZaDatiMaterijal

    AFTER INSERT ON [dbo].[Stavka_Prijemnice]
    REFERENCING NEW

    TABLE AS N_TABLE

    FOR EACH STATEMENT MODE DB2SQL

    BEGIN
    DECLARE

    @return_value int,
    @kolicina

    decimal(18, 0)
    EXEC

    @return_value = [dbo].[sumiraj]

    @materijal_id = 2,
    @kolicina

    = @kolicina OUTPUT
    SELECT

    @kolicina as N'@kolicina'
    SELECT

    'Return Value' = @return_value

    end

    .....
    and prcedure is
    USE [graditelj2]
    GO

    /****** Object: StoredProcedure [dbo].[sumiraj] Script Date: 03/12/2011 10:22:44 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    create procedure [dbo].[sumiraj]
    (@materijal_id int,
    @kolicina decimal OUTPUT)
    AS

    SELECT @kolicina= SUM(kolicina)
    from Stavka_Prijemnice
    where materijal_id = @materijal_id
    return
    GO

  2. #2
    Join Date
    Mar 2011
    Posts
    2

    call prodeure from trigger

    I have 2 tabeles:
    AA
    and
    B
    Table AA have columns id, c1, c2

    Table B have columns id, c1, c2

    table AA
    id c1 c2
    1 1 10
    2 1 10
    3 2 4



    Table B
    id c1 c2
    1 1 ?
    1 2 ?




    I should write procedure that will sumarise AA.C2 for values C1
    for example AA.C1 = 1 sum is 20
    for example Za AA.C1 =2 sum is 4

    Thhis is code for that procedure
    USE [graditelj2]
    GO

    /****** Object: StoredProcedure [dbo].[sumirajAB] Script Date: 03/12/2011 10:38:27 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    create procedure [dbo].[sumirajAB1]
    (@c1 int,
    @kolicina int OUTPUT)
    AS

    SELECT @kolicina= SUM(c2)
    from AA
    where c1 = @c1
    return

    GO
    ………
    And here is what i got
    USE [graditelj2]
    GO

    DECLARE @return_value int,
    @kolicina int

    EXEC @return_value = [dbo].[sumirajAB1]
    @c1 = 2,
    @kolicina = @kolicina OUTPUT

    SELECT @kolicina as N'@kolicina'

    SELECT 'Return Value' = @return_value

    GO
    ………………….

    My problem is:
    I should input in B.C2 values @kolicina from procedure which is called every time i input new row in table AA:



    CREATE

    TRIGGER UbaciSumuAB

    AFTER INSERT ON [dbo].[AA]
    REFERENCING NEW

    TABLE AS N_TABLE

    FOR EACH STATEMENT MODE DB2SQL

    BEGIN
    DECLARE

    @return_value int,
    @kolicina int


    EXEC

    @return_value = [dbo].[sumirajAB1]

    @c1 = 2,
    @kolicina

    = @kolicina OUTPUT
    SELECT

    @kolicina as N'@kolicina'
    SELECT

    'Return Value' = @return_value

    End
    ……

    eroor is

    Incorrect syntax near 'AFTER'.


    i hope that somebody know what is my mistake
    thanks for help
    Last edited by kremica; 03-12-11 at 06:06.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    "CREATE TRIGGER UbaciSumuAB
    AFTER INSERT ON [dbo].[AA]
    REFERENCING NEW TABLE AS N_TABLE
    FOR EACH STATEMENT MODE DB2SQL"

    This is the IBM DB2 syntax, so it is absolutely normal that it does not work on SQL Server. See the right syntax here: CREATE TRIGGER (Transact-SQL)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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