Trigger Call Stored Procedure

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.
LVL 1
emi_sastraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul NeralichBusiness OwnerCommented:
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.
0
HuaMin ChenProblem resolverCommented:
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

0
emi_sastraAuthor Commented:
Hi Paul,

I use MS SQL Server.

I want to create a new on.

Thank you.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

emi_sastraAuthor 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.
0
HuaMin ChenProblem resolverCommented:
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

0
emi_sastraAuthor Commented:
Hi Huamin,

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

Thank you.
0
HuaMin ChenProblem resolverCommented:
Yes.
0
emi_sastraAuthor Commented:
Why just from inserted ?

Thank you.
0
HuaMin ChenProblem resolverCommented:
To detect whatever changes ever inserted or updated.
0
emi_sastraAuthor Commented:
What if it is deleted ?

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

Thank you
0
HuaMin ChenProblem resolverCommented:
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.
0
emi_sastraAuthor 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.
0
HuaMin ChenProblem resolverCommented:
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

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

Thank you.
0
HuaMin ChenProblem resolverCommented:
This is to detect the month change, per your request. No, when it is the same.
0
emi_sastraAuthor 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.
0
HuaMin ChenProblem resolverCommented:
Yes, you can adjust the codes.
0
emi_sastraAuthor 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.
0
HuaMin ChenProblem resolverCommented:
It should be fine.
0
Mark WillsTopic AdvisorCommented:
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....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor 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.
0
Mark WillsTopic AdvisorCommented:
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 ?
0
emi_sastraAuthor 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.
0
Mark WillsTopic AdvisorCommented:
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 ?
0
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.