SQLServerCentral.com Articles tagged Triggers

Solving the SQL Server Multiple Cascade Path Issue with a Trigger

Fri, 24 Aug 2012 06:00:00 UT

This tip will look at how you can use triggers to replace the functionality you get from the ON DELETE CASCADE option of a foreign key constraint.

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

SQL Triggers in a Security Context

Thu, 12 Jul 2012 06:00:00 UT

This article will show you how to use user-defined triggers to supplement your security policies, preventing unauthorised data manipulation and blocking unfriendly logins.

Detecting Changes to a Table

Mon, 23 May 2011 06:00:00 UT

Discussing various techniques for detecting changes to a Table.

Avoid External Dependencies in SQL Server Triggers

Mon, 14 Feb 2011 07:00:00 UT

I sometimes want to perform auditing or other actions in a trigger based on some criteria. More specifically, there are a few cases that may warrant an e-mail; for example, if a web sale takes place that requires custom or overnight shipping and handling. It is tempting to just add code to the trigger that sends an e-mail when these criteria are met. But this can be problematic for two reasons: (1) your users are waiting for that processing to occur, and (2) if you can't send the e-mail, how do you decide whether or not to roll back the transaction, and how do you bring the problem to the attention of the administrator?

Automated Trigger To Require a WHERE Clause

Tue, 25 Jan 2011 07:00:00 UT

Learn how you can build triggers that prevent an update or delete statement from being run without a WHERE clause in this article.

Access variables values from Trigger

Wed, 09 Jun 2010 06:00:00 UT

Pass variables values from Stored Procedures to Trigger

Using INSTEAD OF triggers in SQL Server for DML operations

Tue, 11 Aug 2009 06:00:00 UT

In SQL Server 2000 and onwards there are INSTEAD OF triggers that can be used to carry out such tasks. Although these types of triggers can be used in a number of scenarios their primary function is to perform DML operations through such views. This tip shows how to use INSTEAD OF triggers to handle these DML operations on views.

Instead of Trigger, Part 2 - SQL School Video

Thu, 09 Jul 2009 06:00:00 UT

Part 2 of MVP Andy Warren's SQL School video on Instead of Triggers.

Instead of Trigger, Part 1 - SQL School Video

Tue, 07 Jul 2009 06:00:00 UT

If you have never used an instead of trigger, it's a great mechanism for evading table triggers in certain situations. MVP Andy Warren brings you part one of this SQL School video.

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II

Mon, 18 May 2009 06:00:00 UT

Continuing with his series on using auditing information to roll transactions forward or back, David McKinney shows us how to generate audit triggers using XML.

Filtering DML Statements

Mon, 09 Feb 2009 07:00:00 UT

Filtering DML statements by loginname , host name or application name

Bypassing a Trigger - SQL School Video

Tue, 16 Dec 2008 07:00:00 UT

Triggers are supposed to fire for every update, but in this video we see how you can bypass a trigger for an update.

Scope: The drastic caveat with Logon Triggers.

Mon, 15 Dec 2008 07:00:00 UT

In this article, Johan Bijnens shows how logon triggers can bite you in the back.

Connecting to SQL Server with a Bad Logon Trigger

Tue, 02 Dec 2008 07:00:00 UT

One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?

Introduction to DML Triggers

Tue, 14 Oct 2008 06:00:00 UT

This article by Jack Corbett explains what triggers are, the different types, and how to avoid common mistakes.

Disabling a Trigger for a Specific SQL Statement or Session

Tue, 07 Oct 2008 06:00:00 UT

I want to suppress the trigger from firing for a particular statement while it remains in its normal execution state for any of the other statements. Is there any way this can be done dynamically?

Comparing SQL Server constraints and DML triggers

Wed, 23 Jul 2008 06:00:00 UT

Over the years, I have witnessed many developers being confused about when to use data-manipulation language (DML) triggers vs. when to use constraints.

Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

DML Trigger Status Alerts

Tue, 15 Jul 2008 06:00:00 UT

When databases suddenly stop working, it can be for a number of different reasons. Human error plays a large part, of course, and the DBA needs to know what these various humans are up to. DDL triggers can help alert the DBA to unauthorized tampering with a production system, of course, but DDL triggers can't tell you everything. At some point, you will need to implement your own checks. Randy certainly reached that point!

Row-By-Row Processing Without Cursor

Fri, 28 Dec 2007 07:00:00 UT

If you are a SQL Server DBA, you realize that the performance of cursors is not great and they should be avoided where possible. One place it is difficult to avoid cursors is individual row processing within a trigger. Amin Sobati brings us a new article that shows just how you can avoid this.

Row-By-Row Processing Without Cursor

Fri, 28 Dec 2007 07:00:00 UT

Trigger Trivia

Tue, 13 Nov 2007 07:00:00 UT

From SQL Server trainer and guru Andy Warren, he are a few short nuggets that you might not realize about triggers.

DDL Triggers in SQL Server 2005

Mon, 24 Sep 2007 06:00:00 UT

DDL Triggers are key improvement in SQL Server auditing, allowing tracking of events such as the creation of a new table, database or user.

Using DDL Triggers in SQL Server 2005 to Capture Schema Changes

Tue, 28 Aug 2007 06:00:00 UT

This article shows how to use DDL triggers to capture schema changes. This solutions works a little differently than most DDL triggers examples. This script captures the old object that was replaced.

Row-By-Row Processing Without Cursor

Fri, 28 Dec 2007 07:00:00 UT

Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

Creating a generic audit trigger with SQL 2005 CLR

Wed, 02 Aug 2006 06:00:00 UT

Auditing is becoming more important all the time for DBAs as regulations and requirements increase. Building auditing into your systems can be done a number of ways, but with SQL Server 2005, you have a new option. New author David Ziffer brings us a generic auditing CLR trigger.

Using DDL Database Triggers

Thu, 20 Jul 2006 06:00:00 UT

In this short demonstration, you'll learn how to use DDL database triggers to prevent changes to product and audit changes to comply with Sarbanes-Oxley and other change management requirements. Free registration required.

Stored Procedure vs Triggers

Fri, 31 Mar 2006 06:00:00 UT

Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar.

Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

Triggers for Auditing

Tue, 29 Nov 2005 07:00:00 UT

Have you ever needed to audit your SQL Server 2000 database for changes to a table? A simple auditing solution is presented here by new author Tiago Silva using an INSTEAD OF trigger.

Triggers in SQL Server 7.0 and 2000 - What's New

Fri, 24 Jun 2005 06:00:00 UT

This is the second of a two part series on how triggers work in the two latest versions of MS SQL Server. The first part dealt with what is common between the two versions. This article will detail what's been added to SQL Server 2000.

Triggers in SQL Server 7.0 and 2000 - The Common Ground

Fri, 21 Jan 2005 07:00:00 UT

This is a two part series on how triggers work in the two latest versions of MS SQL Server. Because there are some pretty significant additions in trigger functionality from 7.0 to 2000, we'll first need to look at what the two versions have in common. The second part to this series will look at the differences between the two MS SQL Server versions.