Guest User

Untitled

a guest
Jan 16th, 2019
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.11 KB | None | 0 0
  1. CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
  2. LoginName varchar(200),
  3. QueryExecuted varchar(max));
  4.  
  5. CREATE TRIGGER SynonymTrigger
  6. ON DATABASE
  7. AFTER DROP_SYNONYM
  8. AS
  9. INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted)
  10. SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
  11.  
  12. CREATE SYNONYM TEST
  13. FOR dbo.test;
  14.  
  15. DROP SYNONYM TEST;
  16.  
  17. SELECT *
  18. FROM DBO.SynonymTriggerLogging;
  19.  
  20. ID LoginName QueryExecuted
  21. 1 rvertongen DROP SYNONYM TEST
  22.  
  23. CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
  24. LoginName varchar(200),
  25. QueryExecuted varchar(max),
  26. HostName varchar(200));
  27. GO
  28. CREATE TRIGGER SynonymTrigger
  29. ON DATABASE
  30. FOR DROP_SYNONYM
  31. AS
  32. INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted,HostName)
  33. SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),HOST_NAME()
Add Comment
Please, Sign In to add comment