April 25, 2016 at 11:29 am
Can I "phone a friend" or "ask the audience"?
April 26, 2016 at 2:34 am
Bill Talada (4/25/2016)
Can I "phone a friend" or "ask the audience"?
Only if you haven't already claimed to be an expert 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 13, 2020 at 11:38 am
I was the lead developer for developing a web application to register health care professionals to serve as volunteers in emergencies and disasters. This is the first time that a DBA was assigned to work on a project; usually when there is a database needed, a DBA is chosen from a team of DBAs. I think that assigning a DBA to work on a project worked great. He designed the table and used triggers for various purposes. One was to create an audit log. Another was to send an email that a new volunteer has registered. There are others that serve different purposes. That web application went into production in 2009 and is still in use. The database has been through a few Windows Server moves and SQL Server moves.
I created a README folder in the web application project to help with various items related to infrastructure. When the database is moved to a new SQL Server, the email profile needs to be added to the msdb database.
August 13, 2020 at 11:55 am
As someone said very early in this thread, if you abuse a SQL Server feature, it will abuse you back! 🙂
I worked on a project once which relied heavily on triggers. The issue in them was that they were written by someone without a knowledge of set-based development. There were lot of procedural coding (conditional branching, loops and cursors) implemented especially when multiple record changes fired a single trigger instance. The client wanted to keep the triggers, but after re-writing them the response delay was over 98% eliminated.
Like implicit data type conversions, triggers are hard to detect and can be deadly if not properly implemented. That's why most people avoid them and why blanket rules of thumb banning them get created. Properly used they can be quite handy in certain circumstances. Just don't try to apply them across the board where they're not appropriate.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
August 13, 2020 at 12:06 pm
I think triggers can be agood. On the database of my previous employer we had tons of triggers to ensure business logic. Of course you could argue, that it should be done in the application layer or by stored procedures etc. but this works only in theory. In reallity you will always come to cases where you have to do a manual insert / update / delete and when you have to manually redo the business logic....
Logging / auditing is another point (and I want to log manual changes too). Yes, SQL server has temporal tables etc., but they do not allow me to log the user, particularly if the application uses only a single service user to authenticate to the SQL server and uses its own security model for authentication / permissions.
Drawback of triggers is of course performance if you insert / update / delete tons of rows, since it uses the TempDB for the internal INSERTED / DELETED pseudo tables, so it would be a bad idea to use them for DWH purposes.
BTW: to temporary disable the trigger I always used
IF @@ROWCOUNT = 0 OR CONTEXT_INFO() = 0x1000 RETURN;
as first line in the trigger. By executing a SET CONTEXT_INFO 0x1000; I was able to "disable" the trigger only for this specific session (while it was still active for everyone else). This often removed the need for an explicit maintenance window (the 0x1000 is only a random value, you can use every other value too or even different values to "disable" only parts of the trigger or specific triggers).
God is real, unless declared integer.
August 13, 2020 at 1:07 pm
It would be nice if the object explorer in SSMS had a node under each database for triggers so that you could see them all in one place.
August 13, 2020 at 4:02 pm
It would be nice if the object explorer in SSMS had a node under each database for triggers so that you could see them all in one place.
Agreed but then you'd need one for indexes, constraints, keys, etc, etc. That would be a lot of visual clutter that would mess with day-to-day use, IMHO.
When troubleshooting for the presence of triggers, it's just too easy to use sys.triggers. You could, for example, turn the following code into a system stored procedure in the master database or just keep it handy as a script or just write it when you need it because it's simple.
SELECT ObjectName = CONCAT(OBJECT_SCHEMA_NAME(parent_id),'.',OBJECT_NAME(parent_id))
,*
FROM sys.triggers
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply