Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Create Procedure prcInsertPrd
- As
- Begin
- Begin Transaction
- Declare @Identity int
- Insert into Temp_ProductsArchive (column1,column2,column3) select
- (column1,column2,column3)
- from Temp_Products
- if(@@Error=0)
- Begin
- Commit
- End
- else
- Begin
- Rollback
- End
- End
- Alter TRIGGER trgInsertTopProducts
- ON Temp_ProductsArchive
- AFTER INSERT
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- declare @LeadId as int
- Select @LeadId=LeadId from inserted
- print @LeadId
- Insert into Temp_ProductsTop(column4,LeadID,Column5,column6) Select column4,@LeadID 'LeadID',column 5,column6 from Temp_Products
- END
- GO
- Alter TRIGGER trgInsertTopProducts
- ON Temp_ProductsArchive
- AFTER INSERT AS
- BEGIN
- SET NOCOUNT ON;
- insert into Temp_ProductsTop
- (ProductID,LeadID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)
- Select ProductID, inserted.LeadID, ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
- from
- Temp_Products
- inner join inserted on inserted.Leadid = ?
- END
- GO
- Alter TRIGGER trgInsertTopProducts
- ON Temp_ProductsArchive
- AFTER INSERT
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- declare @LeadId as int
- declare c CURSOR READ_ONLY for
- select LeadId from inserted
- open c
- fetch next from c into @LeadId
- while @@FETCH_STATUS = 0
- BEGIN
- Insert into Temp_ProductsTop(ProductID,LeadID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) Select ProductID,@LeadID 'LeadID',ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued from Temp_Products
- fetch next from c into @LeadId
- END
- close c
- deallocate c
- END
Add Comment
Please, Sign In to add comment