How many triggers can be added in a table

How many triggers can be added in a table?How many triggers can be added in a table?

From SQL BOL (updated version)

(topic Maximum Capacity Specifications)

Maximum triggers per table: Limited by number of objects in a database4

Note 4: 4 Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Regards,

hmscott|||a better question...

are a lot of triggers on a table a good idea? probably not.sql

How is represented the Updated Table in a Trigger?

Hello

I am a newbie creating triggers and I would like to know what is the name of the updated logical table. That is, since the are identifiers like "inserted" and "deleted" that represent the inserted and deleted rows, respectivaly, I presumed that "updated" existed - but it does not.

I have a table with a column named "UpdatedTimeStamp" which I would like to update anytime the other columns are modified. I intended to create the following trigger:

create trigger myTrigger
on myTable
after update
as

update updated set UpdateTimeStamp=GetDate()

Of course, "updated" is not a valid identifier that represents the updated row.

Please, help me in creating this trigger. What is the correct way of doing a trigger like this?

Thanks a lot in advanced.Do you have books online?

Its inerted and deleted|||I know, but as I stated in my previous post, I do not know how to update a row which has just been updated. For example: I have a table with 2 columns, A and B -where B is a datetime column. I would like that when A has just been updated (not inserted or deleted), B would have the datetime value when that happened. Something like:

update [updated] set B=GetDate()

Of course, [updated] is not valid identifier for a logical table. Can you please help me in how to accomplish this scenary?|||The way you tell a row or rows have been updated is that both the inserted and deleted tables contain data. On an insert, the data only appears in the inserted table. On a delete, the data only appears in the deleted table.

Now with this information, try and put together a trigger for an update, and come back with your results. Also, you might want to include some schema information, so we can see the table or tables you are trying to maintain.

And remember, you want the trigger to update all rows updated, not just one of them. Think about set based operations.|||Do you have books online?

Its inerted :shocked: and deleted

How long have you been working in the bomb disposal unit at your office?

:D

hmscott|||I came up with the following solution:

suppose the existence of a table called myTable, having the columns:
- ID numeric (PK)
- Value varchar(50)
- UpdateTimeStamp datetime

Now, my trigger is

create trigger myTrigger
on myTable
after update
as

if update(Value)
begin
update myTable
set
UpdateTimeStamp=GetDate()
from myTable
inner join inserted i on i.ID=myTable.ID
inner join deleted d on d.ID=myTable.ID
end

Is this code ok?

If I open the table and modify a row, the UpdateTimeStamp column is updated as expected. However, when I update only some rows with a query, for a reason I failed to understand, all the rows get their UpdateTimeStamp field updated.

What could be wrong?|||Personally, I would not bother with the UPDATE() function. It can be misleading, especially when dealing with interfaces that my issue spurious update statements, as it merely indicates whether a column was part of an update statement, and does not indicate whether the value actually changed.

For instance, this code:
update A set Value = 5 where Value = 5
...would result in the UPDATE() function indicating that a set of records were updated, whereas in reality they have the same value as before.

If you really want to check whether the value has changed, try something like this:
begin
update myTable
set UpdateTimeStamp=GetDate()
from myTable
inner join inserted i on i.ID=myTable.ID
inner join deleted d on d.ID=myTable.ID
where inserted.Value <> deleted.Value
or inserted.Value is null and deleted.Value is not null
or inserted.Value is not null and deleted.Value is null
...or use the BINARY_CHECKSUM() function to quickly compare multiple columns.