0

I have looked at these threads and while they are similar, they do not answer my question.

Can't see the triggers that I created in SQL Server Management Studio 2008

Unable to find where triggers are stored in sql server 2008

Where does a Server trigger save in SQL Server?

In SSMS 17.9.1 (SQL Server 2017), I can see the trigger exists when using this code

select * from FocalAreas.sys.triggers

I can also see on the SharingPermissionTest (table where I want the trigger) > View Dependencies, the trigger is there. However, when I expand the SharingPermissionTest trigger folder, there is nothing there. When I expand the Programmability > Database Triggers on the database there is nothing there. When I expand the Server Objects > Triggers there is nothing there. Anybody have any insight into what's going on? This was my SQL to create the trigger:

    USE FocalAreas
GO

CREATE TRIGGER dbo.SharingPermissionsTrigger 
   ON  FocalAreas.dbo.FOCALREFERENCEAREAS
   AFTER INSERT
AS BEGIN
    DECLARE @FocalRefID nvarchar(50)
    DECLARE @StateID nvarchar(2)
    SELECT @FocalRefID = i.FocalRefID
        FROM Inserted i
        WHERE 1=1
    SELECT @StateID = mp.StateID
        FROM Inserted i, FocalAreas.dbo.MonitoringPoint as mp
        WHERE i.FocalRefID = mp.FocalRefID

INSERT INTO FocalAreas.dbo.SharingPermissionsTest
Values 
 (next value for SharingPermissionSequence, @FocalRefID, 'NBTC', @StateID, 
 'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed'
                         ,'Not Allowed'),
(next value for SharingPermissionSequence, @FocalRefID, 'StateWildlifeAgency', @StateID, 
 'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed'
                         ,'Not Allowed'),
(next value for SharingPermissionSequence, @FocalRefID, 'FedPartners', @StateID, 
 'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed'
                         ,'Not Allowed'),
(next value for SharingPermissionSequence, @FocalRefID, 'NGO', @StateID, 
 'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed'
                         ,'Not Allowed'),
(next value for SharingPermissionSequence, @FocalRefID, 'Public', @StateID, 
 'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed',
                         'Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed','Not Allowed'
                         ,'Not Allowed')
END
GO
MKF
  • 426
  • 7
  • 24
  • 1
    Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is not the case. The trigger will fire **once per statement**, so if your `INSERT` that causes this trigger to fire inserts 25 rows, you'll get the trigger fired **once** and the `Inserted` pseudo table will contain 25 rows. Which of those 25 rows will your code select from `Inserted`? It's non-deterministic, you'll get **one arbitrary row** and you will be **ignoring all other rows**. You need to rewrite your trigger to take this into account! – marc_s Jan 02 '19 at 19:05
  • 2
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged – marc_s Jan 02 '19 at 19:05
  • @marc_s - I am new to triggers and SQL (this is my first attempt at making one), so I'm trying to understand. For my case, only one row will ever be inserted into FOCALREFERENCEAREAS at a time (it's impossible to ever insert more than one at a time), so the Insert should only add the five rows specified to SharingPermissionsTest on trigger. It's certainly possible that the logic in my SQL statement does not reflect this idea though. – MKF Jan 02 '19 at 19:11
  • 1
    Another bad habit - using 3 part names. You won't realize this until you (or someone else) attempts to create a second "environment" using a database that is not named "FocalAreas". – SMor Jan 02 '19 at 19:16
  • Don't fall into the trap that "only one row will ever be inserted". This is a fallacy. At some point the system may change OR you may need to generate some data. Making triggers set based is not a luxury, it is the only way to write them correctly. – Sean Lange Jan 02 '19 at 19:25

1 Answers1

0

Expand the tree for the TABLE FocalAreas.dbo.FOCALREFERENCEAREAS, right click on the "Triggers" node/folder, click "Refresh", then expand the "Triggers" tree. The trigger should be there.

This is the solution from the first link you posted, by the way, but in the text of your question, you don't describe following this solution correctly. You mention "when I expand the SharingPermissionTest trigger folder, there is nothing there." but you never mention looking under the actual table that the trigger is on, which is what the answer in the linked question tells you to do.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thanks, I did not understand it would make the trigger on FocalReferenceAreas (this is my first time making a trigger). I thought the ON FOCALREFERENCEAREAS would mean when I inserted a row there, then it would insert all the other stuff into SharingPermissionsTest. I will edit my trigger SQL. – MKF Jan 02 '19 at 19:05
  • That IS what it means. But in SSMS, you will find the trigger under the table that it is ON. Not under the table(s) that the code might reference. – Tab Alleman Jan 02 '19 at 19:08
  • Thanks for the clarification! Really appreciate it. – MKF Jan 02 '19 at 19:12