Trigger Call Stored Procedure

Hi emi_sastra,

I have a few questions for you:

1. For which database are you writing the trigger? Oracle, MySql, SQL Server?
2. Does the stored procedure already exist, or do you need help creating that as well?

- Paul N.

You can call SP like

EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers 50;  
GO  

Select allOpen in new window

within triggers.

Here is trigger example

create trigger Employee_trigger
on Employees
after UPDATE, INSERT, DELETE
as
declare @EmpID int,@user varchar(20), @activity varchar(20);
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @activity = 'UPDATE';
    SET @user = SYSTEM_USER;
    SELECT @EmpID = EmployeeID from inserted i;
    INSERT into Emp_Audit(EmpID,Activity, DoneBy) values (@EmpID,@activity,@user);
end

Select allOpen in new window

Hi Paul,

I use MS SQL Server.

I want to create a new on.

Thank you.

Hi HuaMin,

The SP should calculate from detail stock to total stock.
And should just calculate from certain item dan trsdate that is inserted, updated or deleted.

Suppose it is change TrsDate, say from Jan to Feb, it should recalculate Jan and Feb.

Thank you.

Try

create trigger detail_stock_trigger
on detail_stock
After UPDATE, INSERT, DELETE
as
begin
declare @m1 varchar(20), @m2 varchar(20), @i1 varchar(20),@tot decimal(17,2);
Set @m1="",@i1=""

select @i1=item,@m2=substring(convert(varchar,trsdate,112),1,6) from inserted
select @m1=substring(convert(varchar,trsdate,112),1,6) from detail_stock where item=@i1
select @tot=sum(qty) from detail_stock where yearmonth=@m2

if @m1<>@m2
begin
	begin tran
		update total_stock qty=@tot where yearmonth=@m2 and item=@i1
	commit tran
end

end

Select allOpen in new window

Hi Huamin,

- select @i1=item,@m2=substring(convert(varchar,trsdate,112),1,6) from inserted
Is this for inserted only ?

Thank you.

Yes.

Why just from inserted ?

Thank you.

To detect whatever changes ever inserted or updated.

What if it is deleted ?

This trigger supposed to handle insert, update and deleted right ?

Thank you

You can try with the current codes. It is supposed to do the re-calculation, when there is a change from inserted/updated/deleted record.

- You can try with the current codes. It is supposed to do the re-calculation, when there is a change from inserted/updated/deleted record.
oK

- select @m1=substring(convert(varchar,trsdate,112),1,6) from detail_stock where item=@i1
This is AFTER, then if the transaction is deletion, could we get the deleted transaction from detail_stock  ?

Thank you.

Try

create trigger detail_stock_trigger
on detail_stock
After UPDATE, INSERT, DELETE
as
begin
declare @m1 varchar(20), @m2 varchar(20), @i1 varchar(20),@tot decimal(17,2),@tot2 decimal(17,2);
Set @m1="",@i1=""

select @i1=item,@m2=substring(convert(varchar,trsdate,112),1,6) from inserted
select @m1=substring(convert(varchar,trsdate,112),1,6) from deleted where item=@i1
select @tot=sum(isnull(qty,0)) from detail_stock where item=@i1 and substring(convert(varchar,trsdate,112),1,6)=isnull(@m1,'')
select @tot2=sum(isnull(qty,0)) from detail_stock where item=@i1 and substring(convert(varchar,trsdate,112),1,6)=isnull(@m2,'')

if isnull(@m1,'')<>isnull(@m2,'')
begin
	begin tran
		update total_stock qty=@tot2 where item=@i1 and yearmonth=@m2
		update total_stock qty=@tot where item=@i1 and yearmonth=@m1
	commit tran
end

end

Select allOpen in new window

- if isnull(@m1,'')<>isnull(@m2,'')
When m1 <> m2 ?
When m1 = m2 ? Should we update total stock ?

Thank you.

This is to detect the month change, per your request. No, when it is the same.

- When m1 = m2 ? Should we update total stock ?
This is to detect the month change, per your request. No, when it is the same.

What if month is the same, but change qty at detail stock ?

Thank you.

Yes, you can adjust the codes.

- Yes, you can adjust the codes.
Ok

What if I delete several rows, with some different items ?
Is your trigger code works ?

Thank you.

It should be fine.

Hi Mark,

Great explanation and sample.
Is it better using View than Table ?
I've heard that using index at view is not recommendable ?

Thank you.

Well, it really depends on transaction volumes.

If there are many thousands per hour/day then the sheer volume might suggest Table.

But there are some "gotcha's" in using a permanent table with extremely high transaction counts as well (locking). So, the real question is that extra overhead for each and every stock movement, versus, the extra time to aggregate data for the view.

And part of that deliberation would have to be "How often do we need to access Total Stock" and the timing of those accesses.

If Total Stock is a reporting option, and those reports are typically "an overnight request", or "end of month stock reconciliation"  then I wouldnt hesitate with creating the view.

The more tables carrying essentially the same base load information, the more work you will have to do to ensure that all values balance, and/or, have some contingency / recovery processes to manually intervene if needed. The downside is you would need to do that, test it, and hopefully never need to use it.

You dont have the same problem with a view.

And with an indexed view, it basically persists data on disk, so, with aggregations, it is possible to dramatically improve (select) performance....

But try running the SELECT statement used in the view and measure the response time.

It would help with indexes on the Detail Stock Table....

Do you want to share the full definition of the Stock Detail Table ?

- Do you want to share the full definition of the Stock Detail Table ?
Please see below code :

CREATE TABLE [dbo].[TMSTOKBARANGDETIL](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TipeTransaksi] [dbo].[ERV_TipeTransaksi] NOT NULL,
	[PPN] [char](1) NULL CONSTRAINT [DF__TMSTOKBARAN__PPN__2B20BFD0]  DEFAULT (''),
	[CostNoSeq] [smallint] NULL CONSTRAINT [DF__TMSTOKBAR__CostN__2C14E409]  DEFAULT ((1)),
	[NoTransaksi] [dbo].[ERV_NoTransaksi] NOT NULL CONSTRAINT [DF__TMSTOKBAR__NoTra__2D090842]  DEFAULT (''),
	[TglTransaksi] [datetime] NULL,
	[GdgCode] [dbo].[ERV_GdgCode] NOT NULL,
	[BarangPaketCode] [dbo].[ERV_BarangCode] NULL,
	[BarangCode] [dbo].[ERV_BarangCode] NOT NULL,
	[NoSeq] [smallint] NULL CONSTRAINT [DF__TMSTOKBAR__NoSeq__2DFD2C7B]  DEFAULT ((1)),
	[QtyTransaksiOrigin] [money] NULL CONSTRAINT [DF__TMSTOKBAR__QtyTr__2EF150B4]  DEFAULT ((0)),
	[SatuanTransaksiOrigin] [dbo].[ERV_SatuanCode] NULL,
	[QtyTransaksi] [money] NULL CONSTRAINT [DF__TMSTOKBAR__QtyTr__2FE574ED]  DEFAULT ((0)),
	[ItemUnitCost] [numeric](18, 6) NULL CONSTRAINT [DF__TMSTOKBAR__ItemU__30D99926]  DEFAULT ((0)),
	[NilaiKurs] [money] NULL CONSTRAINT [DF__TMSTOKBAR__Nilai__31CDBD5F]  DEFAULT ((0)),
	[FifoCost] [numeric](18, 6) NULL CONSTRAINT [DF__TMSTOKBAR__FifoC__32C1E198]  DEFAULT ((0)),
	[AvgCost] [numeric](18, 6) NULL CONSTRAINT [DF__TMSTOKBAR__AvgCo__33B605D1]  DEFAULT ((0)),
	[IdStock] [bigint] NULL,
	[NoTransaksiBeli] [dbo].[ERV_NoTransaksi] NULL,
	[SerialNo] [char](40) NULL,
	[WarrantyNo] [dbo].[ERV_WarrantyNo] NULL,
	[ExpiredDate] [datetime] NULL,
	[KdSts] [char](1) NULL CONSTRAINT [DF__TMSTOKBAR__KdSts__34AA2A0A]  DEFAULT (''),
	[KdClose] [char](1) NULL CONSTRAINT [DF__TMSTOKBAR__KdClo__359E4E43]  DEFAULT (''),
	[CrtId] [char](10) NULL CONSTRAINT [DF__TMSTOKBAR__CrtId__3692727C]  DEFAULT (''),
	[CrtDate] [datetime] NULL,
	[UpdId] [char](10) NULL CONSTRAINT [DF__TMSTOKBAR__UpdId__378696B5]  DEFAULT (''),
	[UpdDate] [datetime] NULL,
 CONSTRAINT [PK__TMSTOKBA__3214EC27AFA1C34C] PRIMARY KEY CLUSTERED 
(
	[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]

Select allOpen in new window

Any suggestions ?

Thank you.

Well,  you only have a clustered PK on ID, and for the views and/or trigger, it is trying to access DETAIL STOCK via :

[IdStock]   -- ITEM ?
[TglTransaksi]  -- trsdate ?

and the other column referred to is :

[QtyTransaksi] -- qty ?

with the remaining column from the sample in the question header (which isnt really needed for total stock) is :

[NoTransaksi] -- trsno ?


So, would definitely help having a secondary index on  the STOCK DETAIL table covering ITEM and TRSDATE and maybe include QTY

That way, it would be an index seek (or at least an index scan) rather than a table scan for the view.

Would also be used for the trigger....

But, if going down the route of an Indexed view, you need to understand that the view is essentially materialised, which means, when you insert, update, or delete, it effectively triggers a clustered index update - in some regards a more efficient version of the Trigger as coded above.

So, create the secondary index because it will be of use :

create index idx_detail_stock_items on tbl_Detail_Stock (Item, Trsdate) include (qty)

Select allOpen in new window

Then try the standard view as a first port of call and check the performance.

Then, if the view performance is  OKAY (given how often and when used), there is no need to go further.

Otherwise, you might need to materialise the Total Stock. Start with the Indexed view and measure inserts, updates and delete performance.

Then, as a last resort, Drop the indexed view and create the Trigger instead.

Does that make sense ?

Hi All,

Thank you very much for your help.