Combined Trigger for Insert, Update, and Delete in SQL

Software : SQL

Language : SQL

Goal: To have 1 trigger for insert, update, and delete operation in SQL.

Steps:

• Create 2 tables. Let's name the first table "Component" and the second table "ComponentHist". Every insertion, modification, or deletion of the 'Component' data will be tracked in 'ComponentHist' table. An action will be used to indicate the operation done to the 'Component' table, which will have one of the following value:
• 'I' (insert)
• 'U' (Update)
• 'D' (Delete).

CREATE TABLE [dbo].[Component](

 [ComponentId] int IDENTITY(1,1) NOT NULL,
[ComponentType] varchar(3) NOT NULL,

 [ComponentName] nvarchar(100) NOT NULL,
[ComponentCode] varchar(20) NOT NULL,

 CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED 
[ComponentConfig] nvarchar(200) NULL,
 [Description] nvarchar(500) NULL,
(

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[ComponentId] ASC
) ON [PRIMARY]

CREATE TABLE [dbo].[ComponentHist](

 [ComponentCode] varchar(20) NOT NULL,
[ComponentId] int NOT NULL,
 [ComponentType] varchar(3) NOT NULL,

 [Description] nvarchar(500) NULL,
[ComponentName] nvarchar(100) NOT NULL,
 [ComponentConfig] nvarchar(200) NULL,
 [Action] char(1) NOT NULL,

 [ComponentId] ASC,
[CreatedDate] datetime NOT NULL,
 CONSTRAINT [PK_ComponentHist] PRIMARY KEY CLUSTERED 
(
 [CreatedDate] ASC

) ON [PRIMARY]

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

• Create a trigger

CREATE TRIGGER [dbo].[trg_Component]
ON [dbo].[Component]
WITH ENCRYPTION

BEGIN
AFTER INSERT, UPDATE, DELETE
AS 
 DECLARE @Action char(1)

   WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) THEN 'U'
SET @Action = (
  CASE
   WHEN EXISTS (SELECT * FROM inserted) THEN 'I' 

 INSERT INTO [ComponentHist]
WHEN EXISTS (SELECT * FROM deleted) THEN 'D'
   ELSE NULL 
  END
 )

 SELECT *, @Action, getdate()
 FROM inserted

END

Total Carried Forward and Total Brought Forward in SSRS

Software : SQL Server Business Intelligent Development Studio 2008 | Reporting Services

Language : VB

Goal:

• Get the total amount of particular column in a particular page (total carried forward) and display the amount to the next page (total brought forward).

Total to be carried in page 1

Bring the total in page 1 to be displayed in page 2 

Steps:

• Click Report --> Report Properties --> Code
• Copy and paste the custom code below:

Dim Shared runningTotals As New Collections.Generic.Dictionary(Of Integer, Decimal)

Public Shared Function CarriedForward(ByVal pageNumber As Integer, ByVal pageTotal As Decimal) As Decimal
If Not  runningTotals.ContainsKey(pageNumber) Then
       runningTotals.Add(pageNumber, pageTotal)

   For i As Integer = 1 To pageNumber
Else
       runningTotals.Item(pageNumber) = pageTotal
   End If
 
   Dim total As Decimal = 0.00

            Return Nothing
If  runningTotals.ContainsKey(i) Then
            total +=  runningTotals.Item(i)
      Else
      End If
   Next i
 
   Return total

   For i As Integer = 1 To pageNumber - 1
End Function



Public Shared Function BroughtForward(ByVal pageNumber As Integer) As Decimal
  
   Dim total As Decimal = 0.00

   Return total
If runningTotals.ContainsKey(i) Then
            total += runningTotals.Item(i)
      Else
            Return Nothing
      End If
   Next i
 
End Function

• Add a text box for your total brought forward in report header, and another text box for your total carried forward in report footer.
• In order to call the function, use this following expression:

=Code.BroughtForward(Globals!PageNumber)

=Code.CarriedForward(Globals!PageNumber, Sum(ReportItems!DatasetName.Value)