how to use AfterInsert trigger to calculations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to use AfterInsert trigger to calculations

Author

Topic

jackal
Starting Member

2 Posts

Posted - 2014-02-24 : 05:22:39

hi I was wondering how to perform calculations of a particular field value in a table using After Insert Trigger in sql server 2008.
I was able to create After Insert Trigger for my tables PurchaseOrder_master and PurchaseOrder_trans in sql server 2008.

Given below is structure of my tables
Table1: name: PurchaseOrder_master
Column Name DataType
agn Int(IDENTITY Primary Key not null)
PO_NO varchar(50)
supplier_id bigint
PO_Date date
TAX bigint
row_upd_date datetime
user_id Int
technician_id bigint
status bit

Table2: name: PurchaseOrder_trans
Column Name DataType
po_id bigint
item_id bigint
PO_NO varchar(50)
qty Int
price float
PO_Date date
status bit
user_id int
row_upd_date datetime

Given Below is my After Insert Trigger:


CREATE TRIGGER trgAfterInsert4 ON [dbo].[PurchaseOrder_master] 
FOR INSERT
AS
declare @ag int;
declare @pno varchar(50);
declare @supplier bigint;
declare @pdate date;
declare @tax int;
declare @row_date datetime;
declare @user int;
declare @technician int;
declare @pid int;
declare @item int;
declare @stat bit;
select @ag=i.agn from inserted i;
select @pno=i.PO_NO from inserted i;
select @supplier=i.supplier_id from inserted i;
select @pdate=i.PO_Date from inserted i;
select @tax=i.TAX from inserted i;
select @row_date=i.row_upd_date from inserted i;
select @user=i.[user_id] from inserted i;
select @technician=i.technician_id from inserted i;

insert into PurchaseOrder_trans(PO_NO,PO_Date,po_id,item_id,status,[user_id],row_upd_date)values(@pno,@pdate,@pid,@item,@stat,@user,getdate());

Now my trigger executes well but my problem is i dont get item_id,price and qty values at all when i execute the trigger.Should i need to include price,qty and item_id in my PurchaseOrder_master_table? So that the values i expect will come?
Second my question is suppose i want to perform calculations in qty and price field values how should i do it using After Insert Trigger in sql server 2008? Can anybody help me please with problem. Any help or guidance in solving this problem would be greatly appreciated.

vishal77

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-24 : 13:29:08

A trigger executes the local batch one one step, not by a row-by-row basis.

CREATE TRIGGER	dbo.trgAfterInsert4
ON		dbo.PurchaseOrder_master
AFTER		INSERT
AS

SET NOCOUNT ON;

INSERT	dbo.PurchaseOrder_Trans
	(
		PO_NO,
		PO_Date,
		po_id,
		item_id,
		[status],
		[user_id],
		row_upd_date
	)
SELECT	PO_NO,
	PO_Date,
	po_id,
	item_ID,
	[status],
	[user_id],
	row_upd_date
FROM	inserted;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources