WhosOn Database - Preventing Storage of Chat Transcripts

Sometimes it is necessary for security or privacy reasons to not store the chat transcript in the database. In WhosOn this can easily be achieved by adding a new database trigger to the database.

The following trigger drops the INSERT command if the LineNumber > 1, and for the first line creates an entry to the effect that the transcript is not stored.

The visitor's session information will still be stored, but this could be prevented with another trigger if required.

Just run the below code in SQL Management Studio, in a SQL Query window against the WhosOn database.



CREATE TRIGGER [dbo].[UserTranscriptStop]
ON [dbo].[UserTranscript]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE @LineNum int
SELECT @LineNum = LineNumber FROM INSERTED

IF @LineNum = 1
BEGIN
INSERT INTO UserTranscript (SiteKey, ChatUID, LineNumber, LineTime, OperatorLine, LineText)
SELECT SiteKey, ChatUID, 1, LineTime, OperatorLine, 'Data not stored in database due to DB Settings'
FROM INSERTED
END
END