Update doesn't update if the condition of Instead of update is unfulfilled

  • Hello, i'm newbie in t-sql.. and when i try to catch event before update on trigger sql using instead of update, but i got stuck using this coz the records doesnt update..

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_ar_invoice_detail_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_ar_invoice_detail] DROP CONSTRAINT FK_t_ar_invoice_detail_t_delivery_notes

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_del_note_detail_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_del_note_detail] DROP CONSTRAINT FK_t_del_note_detail_t_delivery_notes

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_item_delivery_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_item_delivery] DROP CONSTRAINT FK_t_item_delivery_t_delivery_notes

    GO

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

    drop table [dbo].[t_delivery_notes]

    GO

    CREATE TABLE [dbo].[t_delivery_notes] (

    [ID] [bigint] IDENTITY (1, 1) NOT NULL ,

    [doc_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [doc_date] [datetime] NOT NULL ,

    [del_date] [datetime] NULL ,

    [comp_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [comp_add_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [input_by] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [input_date] [datetime] NOT NULL GETDATE(),

    [vehicle_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [accept_by] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [accept_date] [datetime] NULL ,

    [reference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [dn_status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL default('00')

    ) ON [PRIMARY]

    GO

    INSERT INTO t_delivery_notes (doc_no,doc_date,del_date,comp_code,comp_add_code, input_by, vehicle_no) values ('01',getdate(),getdate(),'C01','C01.02','ADMIN','XZ 1029 V')

    CREATE TRIGGER tg_test_dn ON [dbo].[t_delivery_notes]

    INSTEAD OF UPDATE

    AS

    DECLARE @id bigint

    DECLARE @accept_by_edited varchar(15)

    DECLARE @accept_by varchar(15)

    select @ID = ID, @accept_by_edited = accept_by from inserted

    BEGIN

    BEGIN TRAN

    -- CEK APAKAH YANG ACCEPT BY NYA DARI NULL MNJADI NOT NULL

    SELECT @accept_by = accept_by from t_delivery_notes where ID = @id

    IF NOT @accept_by is null --JIKA TIDAK NULL

    begin

    set @accept_by = 'TEST'

    END

    ELSEBEGIN

    UPDATE t_delivery_notes set accept_by = @accept_by_edited + ' TEST' WHERE ID = @ID

    COMMIT

    END

    END

    NB : Sorry if my script is wrong, i hope you guys can advice me about this function,, Thx

  • Have a look at this page. Your trigger is INSTEAD OF UPDATE, but you're doing an INSERT.

    John

  • but, i've created a lot of trigger that used for updated using INSERTED and take no effect..

    :doze:

  • So there are other triggers on the table? Please will you post the definitions for them?

    John

  • CREATE TRIGGER tg_update_stok_dn_reject ON dbo.t_delivery_notes

    FOR UPDATE

    AS

    /*

    fungsi: update status stok '40'-Good Received from Customer, I0050=in from customer reject

    author: erlansyah

    */

    DECLARE @DNid bigint

    DECLARE @DNno varchar(15)

    DECLARE @mat_code varchar(15)

    DECLARE @unit_conv int

    DECLARE @qty int

    DECLARE @wh_code varchar(15)

    DECLARE @dn_status varchar(2)

    select @DNid=ID, @DNno=doc_no, @dn_status=dn_status from inserted

    --get order delivery

    if @dn_status='02'

    begin

    DECLARE DN_cursor CURSOR FOR

    SELECT mat_code, unit_conv, qty

    FROM t_del_note_detail

    WHERE ID=@DNid

    OPEN DN_cursor

    FETCH NEXT FROM DN_cursor

    INTO @mat_code, @unit_conv, @qty

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select @wh_code=wh_code from t_inventories where mat_code=@mat_code

    --insert stock from '40'-Good Received from Customer, I0050=in from customer reject

    --insert into t_inventories(wh_code,mat_code,inv_type_code,in_out,qty,input_by,inv_status,description)

    --values(@wh_code,@mat_code,'I0050',1,@qty*@unit_conv,'system','40','trigger: dbo.t_delivery_notes.tg_update_stok_dn_reject')

    FETCH NEXT FROM DN_cursor

    INTO @mat_code, @unit_conv, @qty

    END

    CLOSE DN_cursor

    DEALLOCATE DN_cursor

    end

    CREATE TRIGGER tg_update_stok_10_to_20 ON dbo.t_delivery_notes

    FOR UPDATE

    AS

    /*

    fungsi: update status stok from '10'-stock transit to customer, to '20'-stock in customer

    author: erlansyah

    */

    DECLARE @accept_by varchar(50)

    DECLARE @DNno varchar(15)

    DECLARE @mat_code varchar(15)

    DECLARE @delivery_date datetime

    DECLARE @qty_assign_check int

    DECLARE @wh_code varchar(15)

    select @DNno=doc_no, @accept_by=accept_by from inserted

    --get order delivery

    if @accept_by is not null

    begin

    DECLARE v_inventory_book_to_cust_cursor CURSOR FOR

    SELECT mat_code, delivery_date, qty_assign_check

    FROM v_inventory_book_to_cust

    WHERE doc_no=@DNno

    OPEN v_inventory_book_to_cust_cursor

    FETCH NEXT FROM v_inventory_book_to_cust_cursor

    INTO @mat_code, @delivery_date, @qty_assign_check

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --update stock from '10'-stock transit to customer, to '20'-stock in customer

    /*

    update t_inventories set inv_status='20'

    where mat_code=@mat_code and trx_date=@delivery_date and qty=@qty_assign_check*-1 and inv_status='10' and inv_type_code='I0070'

    */

    FETCH NEXT FROM v_inventory_book_to_cust_cursor

    INTO @mat_code, @delivery_date, @qty_assign_check

    END

    CLOSE v_inventory_book_to_cust_cursor

    DEALLOCATE v_inventory_book_to_cust_cursor

    end

    *I think that triggers is have no effect for my instead of update trigger.. 🙂

  • Those are FOR UPDATE triggers. They will only fire when an UPDATE statement is executed.

    John

  • i knew it, those triggers is works until i added the Instead Of Update trigger on that table,,,

    but b'coz i dont understand too much about instead of update function, so i don't know what should i change in my instead of update trigger..

  • xmozart.ryan (12/17/2012)


    i knew it, those triggers is works until i added the Instead Of Update trigger on that table,,,

    but b'coz i dont understand too much about instead of update function, so i don't know what should i change in my instead of update trigger..

    Did you get a chance to look at the URL provided by John for INSTEAD OF INSERT Triggers ?

  • rhythm.varshney (12/17/2012)


    xmozart.ryan (12/17/2012)


    i knew it, those triggers is works until i added the Instead Of Update trigger on that table,,,

    but b'coz i dont understand too much about instead of update function, so i don't know what should i change in my instead of update trigger..

    Did you get a chance to look at the URL provided by John for INSTEAD OF INSERT Triggers ?

    Whoops. sry i just check that link..

    but i can't read english well so when i see that link, it's make me :crazy:..

    and my table is have "cascade delete", and have identity value for col ID...

    so where's the problem, ?

    is it because i use the inserted script ? or what ?

  • xmozart.ryan (12/17/2012)


    rhythm.varshney (12/17/2012)


    xmozart.ryan (12/17/2012)


    i knew it, those triggers is works until i added the Instead Of Update trigger on that table,,,

    but b'coz i dont understand too much about instead of update function, so i don't know what should i change in my instead of update trigger..

    Did you get a chance to look at the URL provided by John for INSTEAD OF INSERT Triggers ?

    Whoops. sry i just check that link..

    but i can't read english well so when i see that link, it's make me :crazy:..

    and my table is have "cascade delete", and have identity value for col ID...

    so where's the problem, ?

    is it because i use the inserted script ? or what ?

    Two things.

    1. Do you want to create trigger for UPDATE statement or INSERT statement ?

    2. You mentioned that you are trying to build an update trigger which will execute for UPDATE only not for INSERT.

    So if you want to capture events for INSERT use INSERT trigger and likewise other.

  • xmozart.ryan (12/17/2012)


    Hello, i'm newbie in t-sql.. and when i try to catch event before update on trigger sql using instead of update, but i got stuck using this coz the records doesnt update..

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_ar_invoice_detail_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_ar_invoice_detail] DROP CONSTRAINT FK_t_ar_invoice_detail_t_delivery_notes

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_del_note_detail_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_del_note_detail] DROP CONSTRAINT FK_t_del_note_detail_t_delivery_notes

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t_item_delivery_t_delivery_notes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[t_item_delivery] DROP CONSTRAINT FK_t_item_delivery_t_delivery_notes

    GO

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

    drop table [dbo].[t_delivery_notes]

    GO

    CREATE TABLE [dbo].[t_delivery_notes] (

    [ID] [bigint] IDENTITY (1, 1) NOT NULL ,

    [doc_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [doc_date] [datetime] NOT NULL ,

    [del_date] [datetime] NULL ,

    [comp_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [comp_add_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [input_by] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [input_date] [datetime] NOT NULL GETDATE(),

    [vehicle_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [accept_by] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [accept_date] [datetime] NULL ,

    [reference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [dn_status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL default('00')

    ) ON [PRIMARY]

    GO

    Look here:

    INSERT INTO t_delivery_notes (doc_no,doc_date,del_date,comp_code,comp_add_code, input_by, vehicle_no) values ('01',getdate(),getdate(),'C01','C01.02','ADMIN','XZ 1029 V')

    CREATE TRIGGER tg_test_dn ON [dbo].[t_delivery_notes]

    INSTEAD OF UPDATE

    AS

    DECLARE @id bigint

    DECLARE @accept_by_edited varchar(15)

    DECLARE @accept_by varchar(15)

    select @ID = ID, @accept_by_edited = accept_by from inserted

    BEGIN

    BEGIN TRAN

    -- CEK APAKAH YANG ACCEPT BY NYA DARI NULL MNJADI NOT NULL

    SELECT @accept_by = accept_by from t_delivery_notes where ID = @id

    IF NOT @accept_by is null --JIKA TIDAK NULL

    begin

    set @accept_by = 'TEST'

    END

    ELSEBEGIN

    UPDATE t_delivery_notes set accept_by = @accept_by_edited + ' TEST' WHERE ID = @ID

    COMMIT

    END

    END

    NB : Sorry if my script is wrong, i hope you guys can advice me about this function,, Thx

    Look with eye. Look in the quoted part above where it says "look here:", you are doing an INSERT into the table with this code, not an UPDATE. This has nothing to do with using the inserted table in a trigger.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply