SQL Server 2014 Triggers – Creation and Management

sql server

I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source system and then move that data into a Staging schema to begin the ETL process. I am truncating the landing tables after I have moved that days data into Staging. I am using Hashbytes in a Trigger which will add a hash value to each record for comparison while being moved upstream.

CREATE TRIGGER trgr_hashval_mara ON land.mara
FOR UPDATE, INSERT AS
UPDATE land.mara
SET HashVal = (SELECT hashbytes('md5', (SELECT material, MaterialDesc FOR xml raw)));

I can create these all day long, but I am unable to Drop them using conventional syntax as below. Executing the DROP statement produces an error. 

 DROP TRIGGER trgr_hashval_mara;

"Can't find trigger or you don't have permission to drop trigger."

However the following statement comes back with a list of Triggers I have created.

select * from ibscm01.sys.triggers

I also can't seem to find them in the DB Tree. I see the Database Triggers child, but there are no Items/triggers shown. I am using Toad for SQL server 6.7 which should be the current release. also using Windows Authentication with AD and I have dbo privileges.

enter image description here

Thanks,
Pat

Best Answer

Triggers will be in the same Schema as the Table that they are created on. This is either handled automatically if the Schema is not specified in the CREATE TRIGGER statement, or it is enforced if it is specified but not the same as the Schema of the Table. The MSDN documentation for CREATE TRIGGER states:

DML triggers are scoped to the schema of the table or view on which they are created.

And a simple test confirms the enforcement of this rule:

CREATE SCHEMA [TriggerTest];
GO

CREATE TABLE dbo.TriggerSchemaTest (Col1 INT);
GO

CREATE TRIGGER [TriggerTest].[DifferentSchemaThanTable]
ON [dbo].[TriggerSchemaTest]
AFTER INSERT
AS
BEGIN
  DECLARE @DoNothing INT;
END;
GO

produces the following error:

Msg 2103, Level 15, State 1, Procedure DifferentSchemaThanTable, Line 6
Cannot create trigger 'TriggerTest.DifferentSchemaThanTable' because its schema is different from the schema of the target table or view.

The syntax for dropping a Trigger, according to the SQL Server 2012 & 2014 MSDN page for DROP TRIGGER, is (essentially):

DROP TRIGGER [schema_name.]trigger_name;

So, if you want to know what Schema a particular Trigger (and its parent Table) is in, use the following query:

SELECT ss.[name] AS [TableAndTriggerSchemaName],
       so.[name] AS [TriggerName],
       st.[name] AS [TableName]
FROM   sys.objects so
INNER JOIN sys.schemas ss
        ON ss.[schema_id] = so.[schema_id]
INNER JOIN sys.tables st
        ON st.[object_id] = so.[parent_object_id]
WHERE  so.[name] = N'trgr_hashval_mara';

Also:

  • This is one reason (readability) why it is best to always specify the Schema name when creating Schema-bound objects (rather than relying upon default behavior). Hence you should be using: CREATE TRIGGER land.trgr_hashval_mara ON land.mara...
  • If that is your entire, actual Trigger code, there is a huge problem: it is updating all rows of the table for each and every INSERT and UPDATE statement. Is that really what is desired? You need to be joining to the INSERTED pseudo-table so that you only update rows that are actually changing.
  • You might want to add SET NOCOUNT ON; at the top of each Trigger.
Related Question