November 24, 2016 at 12:31 am
Hi all!
I have app. 200 databases on the same server.
The general layout is is identical for all databases.
They belong to app. 150 different customers.
At one and only one customer, we find on very long intervals, that a table named Personale (i.e. employee) has no records. There should be app. 100 posts.
The program, which uses this database, has no delete functions at all for this table.
We have set up a trigger (I have replaced confidential info with xxxxx:
USE [bbj_xxxxxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_On_Delete_Personal]
ON [dbo].[PERSONAL]
INSTEAD OF DELETE
AS
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SET NOCOUNT ON;
DECLARE @b-2 VARCHAR(5000) = '';
SELECT @b-2 = @b-2 + ',' + CONVERT(VARCHAR(12), per_personal_id) + ' [' + rtrim(ltrim(per_fornavn)) + ' ' + rtrim(ltrim(per_efternavn)) + ']'
FROM deleted;
SET @b-2 = db_name() + ' : En bruger forsøgte at slette følgende post i BBJ_OESTERAGERGAARD.PERSONAL:'
+ CHAR(10) + @b-2 + CHAR(10) + ' UserID: ' + SYSTEM_USER;
ROLLBACK TRANSACTION; -- should probably check @@TRANCOUNT first!
EXEC msdb.dbo.sp_send_dbmail
@from_address=N'Datasign Info <xxx@xxxxxx.dk>',
@profile_name=N'FBL DBMail Profile',
@recipients = 'xx@xxxxxx.dk;xxx@xxxxxx.dk',
@body = @b-2,
@subject = 'Attemt to delete an employee.';
RAISERROR(Cannot delete emplouee(s)', 16, 1);
END
RETURN;
END
but this trigger has not shown anything yet.
What to look for?
Best regards
Edvard Korsbæk
November 24, 2016 at 2:41 am
TRUNCATE TABLE
DROP TABLE/CREATE TABLE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply