SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to use AfterInsert trigger to calculations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jackal
Starting Member

India
2 Posts

Posted - 02/24/2014 :  05:22:39  Show Profile  Reply with Quote
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

Sweden
30113 Posts

Posted - 02/24/2014 :  13:29:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000