Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [interslisko5]
- GO
- /****** Object: StoredProcedure [dbo].[csp_afterupdate] Script Date: 2016-08-24 10:34:37 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- This procedure is run after each add/update/delete of data in LIME.
- -- IMPORTANT: Always avoid cursors if possible!
- -- Version 5.2.0
- ALTER PROCEDURE [dbo].[csp_afterupdate]
- @@sessionidentifier NVARCHAR(64) = N''
- , @@iduser INT = NULL
- , @@transactionid UNIQUEIDENTIFIER
- AS
- BEGIN
- -- Variables needed
- DECLARE @recordcount INT
- DECLARE @idrecord INT
- DECLARE @NEW INT
- DECLARE @updated INT
- DECLARE @deleted INT
- -- Table to hold data from the updatelog table
- DECLARE @temptable TABLE
- (
- [TABLE] nvarchar(64),
- [idrecord] INT,
- [NEW] INT,
- [updated] INT,
- [deleted] INT,
- [USER] INT
- )
- -- Find data to work with
- INSERT INTO @temptable([TABLE], [idrecord], [NEW], [updated], [deleted], [USER])
- SELECT [TABLE], [idrecord], [NEW], [updated], [deleted], [iduser]
- FROM updatelog ul
- WHERE transactionid = @@transactionid
- AND ul.[TABLE] IN ('deal')
- -- SELECT [table]
- -- , [idrecord]
- -- , [new]
- -- , [updated]
- -- , [deleted]
- -- , [relation]
- -- FROM [updatelog]
- -- WHERE [transactionid] = @@transactionid
- -- Start doing stuff if we should
- -- SELECT @relevantrecordscount = COUNT(*)
- -- FROM @temptable
- -- WHERE [table] IN (N'company') -- ##TODO: Add all tables that there is code for in this procedure
- -- IF @relevantrecordscount > 0
- BEGIN
- -- ##TODO: The line below can be removed when adding other code!
- -- Just a place holder since there must be some piece of code between BEGIN and END.
- IF EXISTS
- (
- SELECT 0 FROM @temptable WHERE [TABLE] = 'deal' AND ([NEW] = 1 OR [updated]=1)
- )
- UPDATE c
- SET revenueold = (SELECT SUM(VALUE) FROM deal b INNER JOIN @temptable t ON b.iddeal = t.[idrecord] INNER JOIN company c ON c.idcompany = b.company
- WHERE company = c.idcompany AND b.quotesent >= DateAdd(YEAR, -2, GetDate()) AND b.quotesent < DateAdd(YEAR, -1, GetDate()) AND b.probability = 1)
- , revenuenew = (SELECT SUM(VALUE) FROM deal b INNER JOIN @temptable t ON b.iddeal = t.[idrecord] INNER JOIN company c ON c.idcompany = b.company
- WHERE company = c.idcompany AND b.quotesent >= DateAdd(YEAR, -1, GetDate()) AND b.probability = 1)
- FROM company c
- INNER JOIN deal b ON c.idcompany=b.company
- INNER JOIN @temptable t ON b.iddeal= t.[idrecord]
- WHERE t.[TABLE] = 'deal' AND (t.[NEW] = 1 OR t.[updated] =1)
- AND c.[STATUS] = 0
- END
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement