Number of Trigger used on a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Number of Trigger used on a table

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2007-06-13 : 23:15:37

Hi, is it right to assume that I can only create 1 trigger for INSERT, 1 trigger for UPDATE and 1 trigger for DELETE?

Thanks!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-13 : 23:49:51

Not really if you use instead of triggers as well.

oceanboy
Starting Member

44 Posts

Posted - 2007-06-14 : 00:05:48

But I can't have like
1 FOR/AFTER INSERT
1 INSTEAD OF INSERT

Right?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 00:13:25

You can more than one insert trigger if this is what you are asking

Try these code yourself


create table t1
(
	pk		int identity(1,1),
	col		int,
	upd_time	datetime,
	upd_user	varchar(100)
)

create trigger t1_i1 on t1 for insert
as
begin
	update 	u
	set	upd_time	= getdate()
	from	inserted i inner join t1 u
		on	i.pk	= u.pk
end

create trigger t1_i2 on t1 for insert
as
begin
	update 	u
	set	upd_user	= system_user
	from	inserted i inner join t1 u
		on	i.pk	= u.pk
end

insert into t1 (col) 
select 	10	union all
select	20

select * from t1

oceanboy
Starting Member

44 Posts

Posted - 2007-06-14 : 00:36:58

so that means both of the insert trigger will be fired when the INSERT is called. Well if so, can't we just combine both insert trigger into 1?

Also, that "update u" in each trigger, i presume that "u" is for the table where the calling INSERT is affecting but can i use other character instead of "u" or is it a rule?

I am very confused with triggers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 00:42:30

quote:
can't we just combine both insert trigger into 1?

Yes you can. I am just showing you that you can more than one INSERT trigger attached to a table

quote:
Also, that "update u" in each trigger, i presume that "u" is for the table where the calling INSERT is affecting but can i use other character instead of "u" or is it a rule?

the u is the table alias


	update 	u
	set	upd_user	= system_user
	from	inserted i inner join t1 u
		on	i.pk	= u.pk

quote:
I am very confused with triggers.

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.sql-server-performance.com/nn_triggers.asp

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-14 : 01:44:46

what is maximum no of triggers can be define of one a table..?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 01:48:21

see http://msdn2.microsoft.com/en-us/library/ms143432(SQL.90).aspx

oceanboy
Starting Member

44 Posts

Posted - 2007-06-14 : 01:50:28

oh yes! Thanks Bro!

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 03:12:35

"can't we just combine both insert trigger into 1"

Makes sense. However, if you have multiple applications using a single database it can be easier to have multiple, discrete, triggers.

"Also, that "update u" in each trigger, i presume that "u" is for the table where the calling INSERT is affecting but can i use other character instead of "u" or is it a rule?"

We always put in the "AS" keyword for table aliases, because we think it makes the code more readable and helps prevent bugs. But its an optional keyword. So I would have written:


	update 	u
	set	upd_user	= system_user
	from	inserted AS i inner join t1 AS u
		on	i.pk	= u.pk

You can do the same thing with column aliases:


SELECT	MyCol1 AS A,
	MyCol2 AS B, ...

can also be written


SELECT	MyCol1 A,
	MyCol2 B, ...

but I think that's just asking for trouble when you miss a comma and can't see the second column being used as an Alias instead:


SELECT	MyCol1
	MyCol2, ...

Kristen

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-14 : 05:17:36

Khtan:
as per your link the document is stating that

Triggers per table3
= Limited by number of objects in a database

What it measns , can u clear me .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 05:29:31

Triggers per table 3 = Limited by number of objects in a database

3 Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-14 : 05:45:27

SO suppose i have to alreday sum of
Table +SP+UDF+Ruls+Defaults+Constrains =2,147,483,647
Then i can't able declare the any trigger any more on any of the tables or views.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 06:27:02

Yes. You can't. 2,147,483,647 is the maximum number of objects allowed in a database.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 06:43:56

When you have that many number of objects, you should think of splitting your database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources