需要一些Sql Server和一个简单的触发器的帮助

I wish to make a trigger but i'm not sure how to grab the data for whatever caused the trigger.

我想制作一个触发器,但我不知道如何获取触发器的数据。

I have a simlpe table.

我有一张simlpe表。

FooId INT PK NOT NULL IDENTITY
Name VARCHAR(100) NOT NULL

I wish to have a trigger so that when an UPDATE, INSERT or DELETE occurs, i then do the following.

我希望有一个触发器,以便在发生UPDATE,INSERT或DELETE时,然后执行以下操作。

Pseduocode

IF INSERT
    Print 'Insert' & Name
ELSE IF UPDATE
    Print 'Update' & FooId & Name
ELSE IF DELETE
    Print 'Delete' & FooId & Name

Now, I know how to make a trigger for a table. What i don't know how to do is figure out the values based on what the trigger type is.

现在,我知道如何为表格触发。我不知道该怎么做是根据触发类型来确定值。

Can anyone help?

有人可以帮忙吗?

Edit: Not sure if it helps, but db is Sql Server 2008

4 个解决方案

#1

the pseudo table "inserted" contains the new data, and "deleted" table contains the old data.

伪表“inserted”包含新数据,“deleted”表包含旧数据。

You can do something like

你可以做点什么

create trigger mytrigger on mytable for insert, update, delete
as
    if ( select count(*) from inserted ) > 0
        -- insert or update
        select FooId, Name from inserted
    else
        -- delete
        select FooId, Name from deleted

To clarify all the comments made by others, on an insert, the inserted table contains data and deleted is empty. On a delete, the situation is reversed. On an update, deleted and inserted contain the "before" and "after" copy of any updated rows.

为了澄清其他人所做的所有注释,在插入时,inserted表包含数据并且deleted为空。删除时,情况正好相反。在更新时,删除和插入包含任何更新行的“之前”和“之后”副本。

#2

When you are writing a trigger, you have to account for the fact that your trigger may be called by a statement that effects more than one row at a time.

在编写触发器时,必须考虑到触发器可能被一次影响多行的语句调用的事实。

As others have pointed out, you reference the inserted table to get the values of new values of updated or inserted rows, and you reference the deleted table to get the value of deleted rows.

正如其他人所指出的那样,您引用inserted表来获取更新或插入行的新值的值,并引用已删除的表来获取已删除行的值。

#3

SQL triggers provide an implicitly-defined table called "inserted" which returns the affected rows, allowing you to do things like

SQL触发器提供了一个名为“inserted”的隐式定义表,它返回受影响的行,允许您执行类似的操作

UPDATE mytable SET mytimestamp = GETDATE() WHERE id IN (SELECT id FROM inserted)

Regarding your code sample, you'll want to create separate INSERT, UPDATE and DELETE triggers if you are performing separate actions for each.

关于代码示例,如果要对每个代码执行单独的操作,则需要创建单独的INSERT,UPDATE和DELETE触发器。

(At least, this is the case in SQL Server... you didn't specify a platform.)

(至少,在SQL Server中就是这种情况......你没有指定平台。)

#4

On 2008, there is also MERGE command. How do you want to handle it?

2008年,还有MERGE命令。你想怎么处理它?

Starting from 2008, there are four commands you can modify a table with: INSERT, UPDATE, DELETE, and MERGE:

从2008年开始,您可以使用以下命令修改表:INSERT,UPDATE,DELETE和MERGE:

http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2009/03/11/reasons-to-move-to-sql-2008-merge.aspx

What do you want your trigger to do when someone issues a MERGE command against your table?

当有人针对您的表发出MERGE命令时,您希望触发器做什么?

#1