How can records be deleted without activating the delete trigger?
- by Servaas Phlips
Hello there,
Since about a month we are experiencing records that are disappearing from our database without any reason.
(part of) Our database structure is at
http://i.imgur.com/i15nG.png
Now users and credentials can never be deleted. We noticed however that thanks to our backups that unfortanetely users disappeared from the database. The users and credentials that disappear appear to be completely random.
In order to find out which application deletes this records we created triggers with the following checks:
CREATE TRIGGER Credential_SoftDelete ON [Credential]
INSTEAD OF DELETE
AS
DECLARE @message nvarchar(255)
DECLARE @hostName nvarchar(30)
DECLARE @loginName nvarchar(30)
DECLARE @deletedId nvarchar(30)
SELECT @deletedId=credentialid FROM deleted;
SELECT @hostName=host_name,@loginName=login_name FROM sys.dm_exec_sessions WHERE session_id=@@SPID;
SELECT @message = '[FAULT] Credential : ' + USER_NAME() + ' deleted ' +@deletedId + ' on ' + @@SERVERNAME + ' from [' + @hostname + ' by ' + @loginName;
EXEC xp_logevent 50001,@message,ERROR
GO
Now after we added this trigger we hoped to find out which application deletes these credentials by searching in the log files.
Unfortanetely the credentials are still deleted and the trigger Credential_SoftDelete is never logged.
I did try run a delete on the database where the trigger is installed and where the users have disappeared.
I ran the following query on the database:
DELETE FROM [User] WHERE userid=296
and the trigger prevented deletion of this user and also logged this in the log events.
This was actually on exact the same database where the users disappeared. (so no test copy or something like that)
Please note that we also use replication, the type of replication we use is merge replication.
How is this possible? Can the fact that we use replication on this database be the cause of this problem?
