Write Trigger in Microsoft SQL SERVER

Recently i got a project where client use Shoper as POS and HO for managing its sales and purchase. I need to fetch Order along with customer details.

Shoper database in sql server, but it has no relationship among table. My database is one to many relationship between customer and order.

So I write a trigger , When new record inserted in shopper table i fetch this records and insert it into my database after selecting all the desire attribute.

create Trigger customerAudit on stktrndtls

After Insert

AS

Declare @TrnCtrlNo varchar(10);

declare @DocNoPrefix varchar(10);

declare @DocEntNetValue varchar(10);

declare @DocDt datetime;

declare @DocEntTotDisc varchar(10);

declare @StockNo varchar(32);

declare @OrdDocNo int;

declare @DocNo varchar(10);

declare @DiscRate varchar(10);--money

declare @VACompCode varchar(10);

declare @ItemMRPBillTm varchar(10);--money

declare @PhyQtyOut varchar(3);

declare @StkUpdtRate varchar(10);

declare @StkUpdtValueOut Varchar(10);

declare @Class1Cd varchar(16);

declare @Class2Cd varchar(16);

declare @SubClass1Cd varchar(16);

declare @SubClass2Cd varchar(16);

declare @Retail_Price varchar(10);--money

declare @ItemDesc varchar(60);

declare @PartyId varchar(16);

declare @TotDocValue varchar(20);

declare @TotDocDisc varchar(20);

declare @Code varchar(16);

declare @Nm varchar(30);

declare @check varchar(11);

--declare @mddate datetime;

--declare @epoc number(20);

BEGIN

select @TrnCtrlNo=i.TrnCtrlNo from inserted i;

select @DocNoPrefix=i.DocNoPrefix from inserted i;

print @TrnCtrlNo;

select @DocEntNetValue=i.DocEntNetValue from inserted i;

select @DocDt=i.DocDt from inserted i;

select @DocEntTotDisc=i.DocEntTotDisc from inserted i;

select @StockNo=i.StockNo from inserted i;

select @OrdDocNo=i.OrdDocNo from inserted i;

select @DocNo=i.DocNo from inserted i;

select @DiscRate=i.DiscRate from inserted i;

select @VACompCode=i.VACompCode from inserted i;

select @ItemMRPBillTm=i.ItemMRPBillTm from inserted i;

select @PhyQtyOut=i.PhyQtyOut from inserted i;

SELECT @StkUpdtRate=i.StkUpdtRate from inserted i;

select @StkUpdtValueOut=i.StkUpdtValueOut from inserted i;

select @Class1Cd=m.Class1Cd from ItemMaster m where m.StockNo=@StockNo;

select @Class2Cd=m.Class2Cd from ItemMaster m where m.StockNo=@StockNo;

select @SubClass1Cd=m.SubClass1Cd from ItemMaster m where m.StockNo=@StockNo;

select @SubClass2Cd=m.SubClass2Cd from ItemMaster m where m.StockNo=@StockNo;

select @Retail_Price=m.Retail_Price from ItemMaster m where m.StockNo=@StockNo;

select @ItemDesc=m.ItemDesc from ItemMaster m where m.StockNo=@StockNo;

select @PartyId=h.PartyId from stktrnhdr h where h.DocNo=@DocNo;

select @TotDocValue=h.TotDocValue from stktrnhdr h where h.DocNo=@DocNo;

select @TotDocDisc=h.TotDocDisc from stktrnhdr h where h.DocNo=@DocNo;

select @Nm=c.Nm from Customers c where c.Code=@PartyId;

print @NM;

update stktrndtls set unread='N' from inserted;

select @check=o.mobile from fbCustomer o where o.mobile=@PartyId;

if @check is null

--amount varchar(12), bill_number varchar(12),order_date varchar(12), 

--order_time varchar(12),discount varchar(10),channel varchar(10),store_id varchar(10)

insert into fbcustomer(customerName,mobile,amount,bill_number,order_date,order_time,discount,channel,store_id,epoch)

values(@Nm,@PartyId,@TotDocValue,@TrnCtrlNo+@DocNo+@DocNoPrefix,getdate(),convert(varchar(10), GETDATE(), 108),@TotDocDisc,'Retail',@VACompCode,datediff(ss,'1970-01-01 00:00:00',getdate()));

else 

update fbCustomer set customerName=@Nm,mobile=@PartyId,amount=@TotDocValue,bill_number=@TrnCtrlNo+@DocNo+@DocNoPrefix,order_date=getdate(),order_time=convert(varchar(10), GETDATE(), 108),discount=@TotDocDisc,channel='Retail',store_id=@VACompCode,epoch=datediff(ss,'1970-01-01 00:00:00',getdate())where mobile=@PartyId;

/*

mobile varchar(10) not null,

[gTotal] [varchar](50) NULL,

[billNumber] [varchar](50) NULL,

[orderDate] [varchar](50) NULL,

[orderTime] [varchar](50) NULL,

[discount] [varchar](50) NULL,

[catID] [varchar](50) NULL,

[catName] [varchar](50) NULL,

[subCatID] [varchar](50) NULL,

[subCatName] [varchar](50) NULL,

[prodID] [varchar](50) NULL,

[prodName] [varchar](50) NULL,

[qty] [varchar](50) NULL,

[prodPrice] [varchar](50) NULL,

[storeID] [varchar](50) NULL,

[storeName] [varchar](50) NULL,*/

insert into fbOrders(mobile,gTotal,billNumber,orderDate,discount,catID,catName,subCatID,subCatName,prodID,

prodName,qty,prodPrice,storeID,modifiedDate,TrnCtrlNo,DocNo,DocNoPrefix,DiscRate,StkUpdtRate,StkUpdtValueOut)

values(@PartyId,@DocEntNetValue,@TrnCtrlNo+@DocNo+@DocNoPrefix,@DocDt,@DocEntTotDisc,@Class1Cd,

@Class2Cd,@SubClass1Cd,@SubClass2Cd,@StockNo,@ItemDesc,@PhyQtyOut,@Retail_Price,@VACompCode,getdate(),@TrnCtrlNo,@DocNo,@DocNoPrefix,@DiscRate,@StkUpdtRate,@StkUpdtValueOut);

/*insert into xtcust(customerName,mobile,gTotal,billNumber,orderDate,discount,catID,

catName,subCatID,subCatName,prodID,prodName,qty,prodPrice,storeID,modifiedDate,TrnCtrlNo,DocNo,DocNoPrefix,DiscRate,StkUpdtRate,StkUpdtValueOut)

values(@Nm,@PartyId,@DocEntNetValue,@TrnCtrlNo+@DocNo+@DocNoPrefix,@DocDt,@DocEntTotDisc,@Class1Cd,

@Class2Cd,@SubClass1Cd,@SubClass2Cd,@StockNo,@ItemDesc,@PhyQtyOut,@Retail_Price,@VACompCode,getdate(),@TrnCtrlNo,@DocNo,@DocNoPrefix,@DiscRate,@StkUpdtRate,@StkUpdtValueOut);*/

end ;

In this I convert current datetime to epoch format

select datediff(ss,'1970-01-01 00:00:00',getdate()) as epoch;

following query convert 20/11/2017 23:12:02.000 into EPOCH format

select DATEDIFF(s, '1970-01-01 00:00:00', '2017-11-20 23:12:02.000') as EpochTimeStamp

Converting a timestamp to epoch unix format

RETURN 
(CAST(ts AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)
+ (EXTRACT(MINUTE FROM ts) * 60)
+ (EXTRACT(SECOND FROM ts))