sql server 新增TRIGGER

先在northwnd新增customers_log的table


CREATE TRIGGER test_northwnd on NORTHWND.dbo.customers
AFTER UPDATE
AS
  IF UPDATE(address)
    BEGIN
SET NOCOUNT ON;
DECLARE @name VARCHAR(50)
SELECT @name = [ContactName] FROM INSERTED

   INSERT INTO northwnd.dbo.customers_log select * FROM inserted
 END
GO

雖然有出現紅線,但是還是可是新增成功


-- 查詢：現行DB SERVER，有哪些 TRIGGER執行
SELECT SCHEMA_NAME(tb.schema_id) N'結構描述', OBJECT_NAME(t.parent_id) N'Table Name', t.name N'Trigger名稱',  tEV.type_desc '引發Trigger事件'
FROM sys.triggers t INNER JOIN sys.trigger_events tEV ON t.object_id = tEV.object_id
 INNER JOIN sys.tables tb ON t.parent_id = tb.object_id


--刪除Trigger

DROP TRIGGER Trigger名稱




--判斷修改的欄位(password)是不是有變動,如有變動INSERT資料進另一個TABLE

USE [資料庫名稱]
GO
/****** Object:  Trigger [dbo].[syncMandataPassword]    Script Date: 2020/2/15 上午 10:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[syncMandataPassword] on [資料庫名稱].[dbo].[MAN_Data]
AFTER UPDATE
AS
  IF UPDATE([password])
    BEGIN
SET NOCOUNT ON;
DECLARE @newpw VARCHAR(50)
DECLARE @oldpw VARCHAR(50)
SELECT @oldpw = [password] FROM deleted  --修改前資料
SELECT @newpw = [password] FROM INSERTED   --修改後資料
if @oldpw<>@newpw
begin
INSERT INTO golden.dbo.Go_SyncManData_passwd select code,[password] FROM inserted;
end
 END