1

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

  • 2
    Have you tried DROP TRIGGER dbo.trgr_hashval_mara;? Also, is that the exact code of your trigger? – Solomon Rutzky Jul 6 '16 at 0:18
5

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.
  • @MartinSmith Well, then that's why I only joined to sys.schemas once in the query in my answer ;-). If you are referring to my comment on the Question, I was suggesting that because the OP insists that the Trigger is there, yet running your suggested DROP statement didn't work. But yes, I also just saw in the documentation: "_ DML triggers are scoped to the schema of the table or view on which they are created._". I will update tomorrow to reflect the lack of possibility of the schemas being different. – Solomon Rutzky Jul 6 '16 at 5:42
  • Thanks for the help. The Trigger coed is a starting point as I am learning the T-SQL version, hence why I wanted it dropped. As data is landed in the land schema my desire was to assign a hashvalue to each row for comparison to the staging table during the merge upsert. My strengths are in SAP and Java but I do have some SQL and will get it working correctly. Thanks again for the help! – apesa Jul 6 '16 at 16:34
  • 1
    @MartinSmith I wasn't entirely certain of the rule when I first answered, especially since it sounded like the object was there but not dropping by specifying the correct Schema name. I also recall reading that being in the sysadmin role gave you a default schema of dbo. I didn't have time to test, so I played it safe. However, I have just updated to change the wording to be clear about that, and provided a simple example. – Solomon Rutzky Jul 6 '16 at 21:24
  • @apesa Yer welcome. I just added details to my answer to further clarify, so please review the new info :-). – Solomon Rutzky Jul 6 '16 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.