Sql-server – List with all visible meta data in AFTER UPDATE trigger

sql-server-2012trigger

I have the following issue – something is updating particular table columns when such update is not allowed. I was not able to find the object (searching in T-SQL and ASP code, server jobs), so I decided to create AFTER UPDATE trigger on the given table and try to log everything that is possible and will help me to find what is updating the table.

I am wondering what metadata can I access in the trigger – for example: T-SQL statement performing the update?

Also transaction log back up are made on each 15 minutes, so I guess it is possible to detect the change from the backups having particular information like time and transaction id?

Best Answer

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'.

However as @JeroenB said you may have better luck creating a trace or extended events session. You can get the above information and more that way. Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead. You could certainly stick with the trigger though and see if that gets you the information you need. The main thing you could get out of the other options (that I would want to see) is the actual command being used. I haven't found a way to get that out using a trigger I'm afraid.

Related Solutions

Postgresql – Trigger update does not seem to finish

You caused an endless loop by making the trigger BEFORE INSERT ~~OR UPDATE~~ . Use instead:

CREATE FUNCTION update_if_exists()
  RETURNS trigger AS
$keyword$
BEGIN

-- Check whether the keyword alrady exists.
UPDATE keywords
SET    views  = views + NEW.views
      ,visits = visits + NEW.visits
WHERE  keyword = NEW.keyword;

IF FOUND THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;

END
$keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT ON keywords     -- not OR UPDATE!
FOR EACH ROW EXECUTE PROCEDURE update_if_exists();

Sql-server – Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

Few suggestions from my end, Kindly check and let us know

Run SQL Profiler and check for any unusual activities.
Check if the increase in size of the log files happens regulary at or around the same time (If yes, growht in log file might be the result of scheduled job)
Run DBCC LOGINFO and check if the VLF's are active or inactive.
I randomly GUESS the result of the log growth is due to index maintenance activities you might have scheduled.
Lastly, Just for information: You need to change the database recovery model to SIMPLE and then perform the DBCC SHRINKFILE command.

Best Answer

Related Solutions

Postgresql – Trigger update does not seem to finish

Sql-server – Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

Related Question