What are all the triggers used in a database
This is specific with Microsoft SQL Server 2005
Summary
How to get the list of all the triggers in a database with MS SQL server 2005?
- You cannot view all the triggers when using Visual Studio, you must do it one table at a time.
All the triggers
use AdventureWorksLT;
go
Select tbl.name as 'Table',
trig.name as 'Trigger',
trig.create_date as 'Created',
trig.modify_date as 'Modified'
from sys.tables tbl, sys.triggers trig
where tbl.object_id = trig.parent_id
order by tbl.name;
Table Trigger Created Modified
---------------------------------------- --------------------------------------- ----------------------- -----------------------
Address uAddress 2005-10-14 01:59:50.317 2005-10-14 01:59:50.317
AddressType uAddressType 2005-10-14 01:59:50.427 2005-10-14 01:59:50.427
AWBuildVersion uAWBuildVersion 2005-10-14 01:59:50.537 2005-10-14 01:59:50.537
BillOfMaterials uBillOfMaterials 2005-10-14 01:59:50.647 2005-10-14 01:59:50.647
Contact uContact 2005-10-14 01:59:50.757 2005-10-14 01:59:50.757
ContactCreditCard uContactCreditCard 2005-10-14 01:59:50.863 2005-10-14 01:59:50.863
ContactType uContactType 2005-10-14 01:59:50.973 2005-10-14 01:59:50.973
CountryRegion uCountryRegion 2005-10-14 01:59:51.193 2005-10-14 01:59:51.193
CountryRegionCurrency uCountryRegionCurrency 2005-10-14 01:59:51.083 2005-10-14 01:59:51.083
CreditCard uCreditCard 2005-10-14 01:59:51.300 2005-10-14 01:59:51.300
Culture uCulture 2005-10-14 01:59:51.410 2005-10-14 01:59:51.410
Currency uCurrency 2005-10-14 01:59:51.520 2005-10-14 01:59:51.520
CurrencyRate uCurrencyRate 2005-10-14 01:59:51.630 2005-10-14 01:59:51.630
Customer uCustomer 2005-10-14 01:59:51.740 2005-10-14 01:59:51.740
CustomerAddress uCustomerAddress 2005-10-14 01:59:51.850 2005-10-14 01:59:51.850
Department uDepartment 2005-10-14 01:59:51.960 2005-10-14 01:59:51.960
Document uDocument 2005-10-14 01:59:52.067 2005-10-14 01:59:52.067
Employee dEmployee 2005-10-14 01:59:52.177 2005-10-14 01:59:52.177
Employee uEmployee 2005-10-14 01:59:52.287 2005-10-14 01:59:52.287
EmployeeAddress uEmployeeAddress 2005-10-14 01:59:52.397 2005-10-14 01:59:52.397
EmployeeDepartmentHistory uEmployeeDepartmentHistory 2005-10-14 01:59:52.507 2005-10-14 01:59:52.507
EmployeePayHistory uEmployeePayHistory 2005-10-14 01:59:52.613 2005-10-14 01:59:52.613
Illustration uIllustration 2005-10-14 01:59:52.723 2005-10-14 01:59:52.723
Individual iuIndividual 2005-10-14 01:59:52.850 2005-10-14 01:59:52.850
JobCandidate uJobCandidate 2005-10-14 01:59:52.943 2005-10-14 01:59:52.943
Location uLocation 2005-10-14 01:59:53.050 2005-10-14 01:59:53.050
Product uProduct 2005-10-14 01:59:53.160 2005-10-14 01:59:53.160
ProductCategory uProductCategory 2005-10-14 01:59:53.270 2005-10-14 01:59:53.270
ProductCostHistory uProductCostHistory 2005-10-14 01:59:53.380 2005-10-14 01:59:53.380
ProductDescription uProductDescription 2005-10-14 01:59:53.490 2005-10-14 01:59:53.490
ProductDocument uProductDocument 2005-10-14 01:59:53.600 2005-10-14 01:59:53.600
ProductInventory uProductInventory 2005-10-14 01:59:53.710 2005-10-14 01:59:53.710
ProductListPriceHistory uProductListPriceHistory 2005-10-14 01:59:53.820 2005-10-14 01:59:53.820
ProductModel uProductModel 2005-10-14 01:59:53.927 2005-10-14 01:59:53.927
ProductModelIllustration uProductModelIllustration 2005-10-14 01:59:54.037 2005-10-14 01:59:54.037
ProductModelProductDescriptionCulture uProductModelProductDescriptionCulture 2005-10-14 01:59:54.147 2005-10-14 01:59:54.147
ProductPhoto uProductPhoto 2005-10-14 01:59:54.257 2005-10-14 01:59:54.257
ProductProductPhoto uProductProductPhoto 2005-10-14 01:59:54.367 2005-10-14 01:59:54.367
ProductReview uProductReview 2005-10-14 01:59:54.473 2005-10-14 01:59:54.473
ProductSubcategory uProductSubcategory 2005-10-14 01:59:54.583 2005-10-14 01:59:54.583
ProductVendor uProductVendor 2005-10-14 01:59:54.693 2005-10-14 01:59:54.693
PurchaseOrderDetail iPurchaseOrderDetail 2005-10-14 01:59:54.800 2005-10-14 01:59:54.800
PurchaseOrderDetail uPurchaseOrderDetail 2005-10-14 01:59:54.910 2005-10-14 01:59:54.910
PurchaseOrderHeader uPurchaseOrderHeader 2005-10-14 01:59:55.020 2005-10-14 01:59:55.020
SalesOrderDetail iduSalesOrderDetail 2005-10-14 01:59:55.130 2005-10-14 01:59:55.130
SalesOrderHeader uSalesOrderHeader 2005-10-14 01:59:55.240 2005-10-14 01:59:55.240
SalesOrderHeaderSalesReason uSalesOrderHeaderSalesReason 2005-10-14 01:59:55.350 2005-10-14 01:59:55.350
SalesPerson uSalesPerson 2005-10-14 01:59:55.460 2005-10-14 01:59:55.460
SalesPersonQuotaHistory uSalesPersonQuotaHistory 2005-10-14 01:59:55.567 2005-10-14 01:59:55.567
SalesReason uSalesReason 2005-10-14 01:59:55.677 2005-10-14 01:59:55.677
SalesTaxRate uSalesTaxRate 2005-10-14 01:59:55.787 2005-10-14 01:59:55.787
SalesTerritory uSalesTerritory 2005-10-14 01:59:55.897 2005-10-14 01:59:55.897
SalesTerritoryHistory uSalesTerritoryHistory 2005-10-14 01:59:56.007 2005-10-14 01:59:56.007
ScrapReason uScrapReason 2005-10-14 01:59:56.113 2005-10-14 01:59:56.113
Shift uShift 2005-10-14 01:59:56.223 2005-10-14 01:59:56.223
ShipMethod uShipMethod 2005-10-14 01:59:56.333 2005-10-14 01:59:56.333
ShoppingCartItem uShoppingCartItem 2005-10-14 01:59:56.443 2005-10-14 01:59:56.443
SpecialOffer uSpecialOffer 2005-10-14 01:59:56.550 2005-10-14 01:59:56.550
SpecialOfferProduct uSpecialOfferProduct 2005-10-14 01:59:56.660 2005-10-14 01:59:56.660
StateProvince uStateProvince 2005-10-14 01:59:56.770 2005-10-14 01:59:56.770
Store iStore 2005-10-14 01:59:56.880 2005-10-14 01:59:56.880
Store uStore 2005-10-14 01:59:56.990 2005-10-14 01:59:56.990
StoreContact uStoreContact 2005-10-14 01:59:57.100 2005-10-14 01:59:57.100
TransactionHistory uTransactionHistory 2005-10-14 01:59:57.210 2005-10-14 01:59:57.210
TransactionHistoryArchive uTransactionHistoryArchive 2005-10-14 01:59:57.317 2005-10-14 01:59:57.317
UnitMeasure uUnitMeasure 2005-10-14 01:59:57.427 2005-10-14 01:59:57.427
Vendor dVendor 2005-10-14 01:59:57.537 2005-10-14 01:59:57.537
Vendor uVendor 2005-10-14 01:59:57.647 2005-10-14 01:59:57.647
VendorAddress uVendorAddress 2005-10-14 01:59:57.757 2005-10-14 01:59:57.757
VendorContact uVendorContact 2005-10-14 01:59:57.863 2005-10-14 01:59:57.863
WorkOrder iWorkOrder 2005-10-14 01:59:57.973 2005-10-14 01:59:57.973
WorkOrder uWorkOrder 2005-10-14 01:59:58.083 2005-10-14 01:59:58.083
WorkOrderRouting uWorkOrderRouting 2005-10-14 01:59:58.193 2005-10-14 01:59:58.193
(73 row(s) affected)
- Do not forget to first select the database that you want to use.
- This will give you all the triggers for the database selected.
- This will give you all the triggers for each table.