Trigger Call Stored Procedure

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I want to create trigger after INSERT, UPDATE and DELETE at detail stock.
In the trigger I want to call SP to insert, Update and Delete at total stock.

Detail Stock Schema :

1. TrsNo      Char(10)
2. TrsDate   DateTime
3. Item         Char(10)
4. Qty           Money

Total Stock Schema:

1. YearMonth   Char(6)
3. Item               Char(10)
4. Qty                 Money

For Update, I want to check for Qty or TrsDate, and Item.

How could I do this ?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.
HuaMin ChenProblem resolver

Commented:
You can call SP like

EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers 50;  
GO  

Open 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

Open in new window

Author

Commented:
Hi Paul,

I use MS SQL Server.

I want to create a new on.

Thank you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
HuaMin ChenProblem resolver

Commented:
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

Open in new window

Author

Commented:
Hi Huamin,

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

Thank you.
HuaMin ChenProblem resolver

Commented:
Yes.

Author

Commented:
Why just from inserted ?

Thank you.
HuaMin ChenProblem resolver

Commented:
To detect whatever changes ever inserted or updated.

Author

Commented:
What if it is deleted ?

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

Thank you
HuaMin ChenProblem resolver

Commented:
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.

Author

Commented:
- 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.
HuaMin ChenProblem resolver

Commented:
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

Open in new window

Author

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

Thank you.
HuaMin ChenProblem resolver

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

Author

Commented:
- 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.
HuaMin ChenProblem resolver

Commented:
Yes, you can adjust the codes.

Author

Commented:
- Yes, you can adjust the codes.
Ok

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

Thank you.
HuaMin ChenProblem resolver

Commented:
It should be fine.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
In the code examples below, I have made a few assumptions...

Table names I have used are  tbl_Detail_Stock and tbl_Total_Stock. I assume they are indexed appropriately.

For a start, dont have yearmonth as a character string. Make it a datetime, or, date datatype. It will be better when trying to select date based ranges....

You will also note the use of  "dateadd(month,datediff(month,0,trsdate),0) " 
but if using SQL 2012 or more recent, would prefer to use "EOMONTH(trsdate)" as the end of month, and not the start of month.

And, If updating detail stock, then why update total stock ?

You *could* use a view over detail stock to give you the summarised version. For example
create view vw_Total_Stock
as
  Select ITEM, dateadd(month,datediff(month,0,trsdate),0) as YearMonth,sum(qty) as qty
  from tbl_Detail_Stock
  group by item, dateadd(month,datediff(month,0,trsdate),0)
go

-- once created, use as often as you like as if it were a table....

select * from vw_Total_Stock

Open in new window

You can even create indexes on the view.... A couple of requirements when creating indexed views :
create view vw_Stock_Summary with schemabinding
as
  Select ITEM, dateadd(month,datediff(month,0,trsdate),0) as YearMonth,sum(isnull(qty,0)) as qty, count_big(*) as TransactionCount
  from dbo.tbl_Detail_Stock
  group by item, dateadd(month,datediff(month,0,trsdate),0)
go


create unique clustered index idx_vw_Stock_Summary on vw_Stock_Summary (ITEM, YearMonth) 


select * from vw_Stock_Summary

Open in new window

You will note the double checking of ISNULL(), and the use of COUNT_BIG() - required is creating an index on a view that uses GROUP BY.

But, back to your Total Stock as a table, and the Trigger.

For the Trigger, you dont really need a stored procedure, best to keep it within the process of the trigger :
create trigger trg_detail_stock
on tbl_detail_stock
After UPDATE, INSERT, DELETE
as
begin

    set nocount on

	begin tran
		INSERT tbl_Total_Stock (item,yearmonth)
		Select distinct i.item, dateadd(month,datediff(month,0,i.trsdate),0)
		from inserted i
		where not exists (select NULL from tbl_Total_Stock s where s.item = i.item and s.yearmonth = dateadd(month,datediff(month,0,i.trsdate),0))
	commit tran

	begin tran
		update S set qty = isnull(s.qty,0) + (isnull(i.qty,0) - isnull(d.qty,0))
		from tbl_Total_Stock S
		outer apply (select SUM(qty) from inserted i where s.item = i.item and s.yearmonth = dateadd(month,datediff(month,0,i.trsdate),0)) i(qty)
		outer apply (select sum(qty) from deleted d  where s.item = d.item and s.yearmonth = dateadd(month,datediff(month,0,d.trsdate),0)) d(qty) 

	commit tran
end

Open in new window

This will first check the need to create entries for the periods required. Then updates the period....

Author

Commented:
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.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?

Author

Commented:
- 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]

Open in new window


Any suggestions ?

Thank you.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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)

Open 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 ?

Author

Commented:
Hi All,

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial