Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This topic describes how to delete or disable a DML trigger in SQL Server by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To delete or disable a DML trigger, using:
When a trigger is deleted, it is dropped from the current database. The table and the data upon which it is based are not affected. Deleting a table automatically deletes any triggers on the table.
A trigger is enabled by default when it is created.
Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger will not fire when any INSERT, UPDATE, or DELETE statement on which it was programmed is executed. Triggers that are disabled can be reenabled. Enabling a trigger does not re-create it. The trigger fires in the same manner as when it was originally created.
To delete a DML trigger requires ALTER permission on the table or view on which the trigger is defined.
To disable or enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to delete.
Expand Triggers, right-click the trigger to delete, and then click Delete.
In the Delete Object dialog box, verify the trigger to delete, and then click OK.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to disable.
Expand Triggers, right-click the trigger to disable, and then click Disable.
To enable the trigger, click Enable.
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following examples into the query window. Execute the CREATE TRIGGER statement to create the Sales.bonus_reminder
trigger. To delete the trigger, execute the DROP TRIGGER statement.
--Create the trigger.
USE AdventureWorks2022;
GO
IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
DROP TRIGGER Sales.bonus_reminder;
GO
CREATE TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Compensation', 16, 10);
GO
--Delete the trigger.
USE AdventureWorks2022;
GO
IF OBJECT_ID ('Sales.bonus_reminder', 'TR') IS NOT NULL
DROP TRIGGER Sales.bonus_reminder;
GO
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following examples into the query window. Execute the CREATE TRIGGER statement to create the Sales.bonus_reminder
trigger. To disable and enable the trigger, execute the DISABLE TRIGGER and ENABLE TRIGGER statements, respectively.
--Create the trigger.
USE AdventureWorks2022;
GO
IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
DROP TRIGGER Sales.bonus_reminder;
GO
CREATE TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Compensation', 16, 10);
GO
--Disable the trigger.
USE AdventureWorks2022;
GO
DISABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory;
GO
--Enable the trigger.
USE AdventureWorks2022;
GO
ENABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory;
GO
ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)
Get Information About DML Triggers
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniu