December 17, 2012 at 1:33 am
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
December 17, 2012 at 1:57 am
but, i've created a lot of trigger that used for updated using INSERTED and take no effect..
:doze:
December 17, 2012 at 1:59 am
So there are other triggers on the table? Please will you post the definitions for them?
John
December 17, 2012 at 2:03 am
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.. 🙂
December 17, 2012 at 2:12 am
Those are FOR UPDATE triggers. They will only fire when an UPDATE statement is executed.
John
December 17, 2012 at 2:17 am
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..
December 17, 2012 at 2:48 am
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 ?
December 17, 2012 at 3:10 am
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 ?
December 17, 2012 at 4:02 am
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.
December 17, 2012 at 6:32 am
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