How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master

-- Create table variable
DECLARE @TriggerStatus as Table
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;

SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus 

[tweetmeme only_single=”false”]

How to insert records to a table with an identity column

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement.

There are times when you need to keep the ID values during your insert.  Like moving data between environments.

Here is the SQL you need to allow your inserts to work.
First turn on identity_insert


Then do your insert.

Then turn off identity_insert

[tweetmeme only_single=”false”]