January 26, 2018 at 11:46 am
Hi,
I have a table that keep track of the store procedures and it log errors. I want to add the name of the database to the table.
My table looks as follows
CREATE TABLE MetricsMart.dbo.ProcedureLog
(
LogDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
DatabaseID INT,
DatabaseName nvarchar(200),
ObjectID INT,
ProcedureName NVARCHAR(400),
ErrorLine INT,
ErrorMessage NVARCHAR(MAX),
AdditionalInfo NVARCHAR(MAX)
);
I am trying to create a trigger that after each insert on the table, it will add the name of the db to the table, from sys.databases.
Taking in consideration I never created a trigger on my life, nothing is working :crying:
This is my last half intent, as all the previous ones didn't work either.
Please help
CREATE TRIGGER InsertDBDetails_tr
ON MetricsMart
AFTER INSERT
AS
BEGIN
UPDATE ProcedureLog
SET DatabaseName = (SELECT NAME FROM SYS.DATABASES)
FROM INSERTED
January 26, 2018 at 11:58 am
Update the code in stored procedures to log this.
January 26, 2018 at 12:14 pm
that is what at did, but i still don't know how to do that in a trigger :crying:
January 26, 2018 at 2:08 pm
Wait - am I seeing correctly that your sproc actually creates the log record? If so, why isn't it simply putting the database info in the row it creates?
If it isn't, what is creating the initial error log row? Whatever it is should be populating ALL FIELDS! It is TERRIBLY inefficient to insert a row and then turn around an update it with data that you should have had on insert.
Also, look up db_id() and db_name() system functions.
Oh. and why are you storing both the database name and the database id? Same goes for procedure name and object id, right?? Wasted storage with zero benefit.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2018 at 1:45 pm
thanks, I did added it in the code after I posted.
February 5, 2018 at 3:26 pm
TheSQLGuru - Friday, January 26, 2018 2:08 PMWait - am I seeing correctly that your sproc actually creates the log record? If so, why isn't it simply putting the database info in the row it creates?If it isn't, what is creating the initial error log row? Whatever it is should be populating ALL FIELDS! It is TERRIBLY inefficient to insert a row and then turn around an update it with data that you should have had on insert.
Also, look up db_id() and db_name() system functions.
Oh. and why are you storing both the database name and the database id? Same goes for procedure name and object id, right?? Wasted storage with zero benefit.
But the database_id and object_id can both change. Thus, it's really required to store the name to be accurate. And storing the existing id isn't that much space and so not necessarily a terrible idea, just for reference purposes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2018 at 8:03 am
ScottPletcher - Monday, February 5, 2018 3:26 PMTheSQLGuru - Friday, January 26, 2018 2:08 PMWait - am I seeing correctly that your sproc actually creates the log record? If so, why isn't it simply putting the database info in the row it creates?If it isn't, what is creating the initial error log row? Whatever it is should be populating ALL FIELDS! It is TERRIBLY inefficient to insert a row and then turn around an update it with data that you should have had on insert.
Also, look up db_id() and db_name() system functions.
Oh. and why are you storing both the database name and the database id? Same goes for procedure name and object id, right?? Wasted storage with zero benefit.
But the database_id and object_id can both change. Thus, it's really required to store the name to be accurate. And storing the existing id isn't that much space and so not necessarily a terrible idea, just for reference purposes.
Name is all that is needed - agreed - and is obviously what was intended to keep.
Sorry, I completely disagree with storing the id's "just for reference purposes". 8 unnecessary bytes is just that. Full stop.
I note though that I am actually VER HAPPY that most people out there feel the same way as you do! It creates more performance tuning opportunities for me!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2018 at 10:03 am
TheSQLGuru - Tuesday, February 6, 2018 8:03 AMScottPletcher - Monday, February 5, 2018 3:26 PMTheSQLGuru - Friday, January 26, 2018 2:08 PMWait - am I seeing correctly that your sproc actually creates the log record? If so, why isn't it simply putting the database info in the row it creates?If it isn't, what is creating the initial error log row? Whatever it is should be populating ALL FIELDS! It is TERRIBLY inefficient to insert a row and then turn around an update it with data that you should have had on insert.
Also, look up db_id() and db_name() system functions.
Oh. and why are you storing both the database name and the database id? Same goes for procedure name and object id, right?? Wasted storage with zero benefit.
But the database_id and object_id can both change. Thus, it's really required to store the name to be accurate. And storing the existing id isn't that much space and so not necessarily a terrible idea, just for reference purposes.
Name is all that is needed - agreed - and is obviously what was intended to keep.
Sorry, I completely disagree with storing the id's "just for reference purposes". 8 unnecessary bytes is just that. Full stop.
I note though that I am actually VER HAPPY that most people out there feel the same way as you do! It creates more performance tuning opportunities for me!! 😎
Fascinating. You recommend a very inefficient method then say what a great tuner you are! Wow, rather ::crazy::.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2018 at 1:15 pm
ScottPletcher - Tuesday, February 6, 2018 10:03 AMFascinating. You recommend a very inefficient method then say what a great tuner you are! Wow, rather ::crazy::.
I am pretty tired today, so maybe I am missing something. But how is it that storing 8 bytes less (the two ids) as I suggested is very inefficient?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2018 at 2:10 pm
TheSQLGuru - Tuesday, February 6, 2018 1:15 PMScottPletcher - Tuesday, February 6, 2018 10:03 AMFascinating. You recommend a very inefficient method then say what a great tuner you are! Wow, rather ::crazy::.I am pretty tired today, so maybe I am missing something. But how is it that storing 8 bytes less (the two ids) as I suggested is very inefficient?
Storing the names is inefficient. Just create your own id that cannot change and use it. For a few rows, won't matter much. For lots of rows, could be a huge gain. I don't see a need to distinguish object type, so I don't, I just have a single names table that provides the id for any name.
Although even if I chose to store the name, I would also store the original id, since certain internal / trace / relationship tables use only id not the name. Compress the row and it'll be no more than 5 bytes the vast majority of the time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2018 at 3:55 pm
Ahh, an extra de-reference. Works for me.
I never expect compression to be available though because the vast majority of SQL Server instances are not Enterprise Edition (or 2016SP1+, although that compression is limited a bit compared to EE).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 7, 2018 at 7:56 am
TheSQLGuru - Tuesday, February 6, 2018 3:55 PMAhh, an extra de-reference. Works for me.I never expect compression to be available though because the vast majority of SQL Server instances are not Enterprise Edition (or 2016SP1+, although that compression is limited a bit compared to EE).
Guess I'm a bit spoiled there, or at least have a different mindset. The vast majority of our dbs are on Enterprise versions. With the size of some of them, we need it anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2018 at 10:24 am
ScottPletcher - Wednesday, February 7, 2018 7:56 AMTheSQLGuru - Tuesday, February 6, 2018 3:55 PMAhh, an extra de-reference. Works for me.I never expect compression to be available though because the vast majority of SQL Server instances are not Enterprise Edition (or 2016SP1+, although that compression is limited a bit compared to EE).
Guess I'm a bit spoiled there, or at least have a different mindset. The vast majority of our dbs are on Enterprise versions. With the size of some of them, we need it anyway.
Yep. Just stating "vast majority" probably puts you in the outlier category when it comes to SQL Server installations, EE or not. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply