sql 触发器例子

```sql

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

GO

ALTER TRIGGER [dnt_user_add] ON [dbo].[dnt_users]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [dnt_userfields](uid)

SELECT uid FROM inserted;

END

```

接下来，让我们看看删除操作的触发器。当从[dnt_users]表中删除数据时，[dnt_users_del]触发器就会接管任务，从[dnt_userfields]表中删除与已删除用户对应的记录。代码如下：

```sql

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE TRIGGER [dnt_users_del] ON [dnt_users]

AFTER DELETE

AS

BEGIN

SET NOCOUNT ON;

DELETE FROM [dnt_userfields] WHERE [dnt_userfields].uid = deleted.uid;

END

GO

```

让我们一下更新操作的触发器。当对[dnt_users]表进行更新操作时，[dnt_users_up]触发器就会被激活。这个触发器的任务是将更新后的用户的uid信息同步到[dnt_userfields]表中。虽然触发器中没有直接的"updated"表，但我们可以通过inserted和deleted表获取更新前后的数据。代码如下：

```sql

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

ALTER TRIGGER [dnt_users_up] ON [dnt_users]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE [dnt_userfields] SET icq = inserted.uid FROM inserted WHERE [dnt_userfields].uid = inserted.uid;

END

GO

```

通过合理利用inserted和deleted这两个临时表，我们可以在数据库的数据发生变动时，实现各种复杂的同步和操作任务。这就是触发器的魅力所在。