MS SQL Azure – TSQL to identify all Triggers in a database / Drop Trigger TSQL syntax

I had been told that it was better not to use Triggers..

Nonetheless I had to learn the hard way.

So fast forward a period and I’m wanting to remove triggers from a database and I first need to find where I had put them in the first instance.

SELECT AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    , AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'

And running this against your database you should see something like this.

And how you drop the triggers

IF OBJECT_ID ('sampledelete2chargehistory', 'TR') IS NOT NULL  
   DROP TRIGGER sampledelete2chargehistory;

Thanks to Stack Overflow – I still get some answers from it over AI


SQL Server Express – starting to work with Geometry Functions in SQL Server

Firstly I created a database.

This is essentially me trying to implement in SQL Server what I had written about in Postgres here – Using ST_Within in Postgres

Now I create the starting tables.

CREATE TABLE [dbo].[t00001fields](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[fieldname] [varchar](50) NULL,
	[geom] [geometry] NULL


CREATE TABLE [dbo].[t00002plots](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[plotname] [varchar](50) NULL,
	[geom] [geometry] NULL

Now a magic junction table that will identify what plot is within which field when it is run

CREATE VIEW v001FieldPlotJunction AS
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
t00002plots.GEOM.STWithin(t00001fields.GEOM) = 1;

and here’s an example of it running on local in SQL Express

And this is what I am seeing when viewed through QGIS = many of the fields are smaller than the plots and so although 1 2 look like they should be in the view because the plot is not WITHIN the field it does not show which is the correct response in this case.