Trigger BEFORE ! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Trigger BEFORE !

Author

Topic

chris_j_pook
Starting Member

2 Posts

Posted - 2007-06-19 : 11:34:40

Hi,

I have a trigger set on a table that is supposed to fire on update / insert and set all rows to false except the one that just got entered ( if it happened to be true ) ...

However my trigger turns all the rows to false as if it is firing after the update despite the fact im not using AFTER ...

Any ideas ?


USE [masterdb]
GO
ALTER TRIGGER setdefault

ON [dbo].[profile]

AFTER INSERT, UPDATE

AS
	UPDATE [profile]
	SET [default] = 0
	WHERE [default] = 1

Many thanks

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 12:24:16

To "not be using AFTER" you would have to write an INSTEAD OF trigger. They are a bit of a Pain, so suggest you avoid that.

You could do:


	UPDATE	U
	SET	[default] = 0
	FROM	[profile] AS U
	WHERE	[default] = 1
		AND U.MyPK NOT IN (SELECT MyPK FROM inserted)

to update all-but the row you have just added / updated.

Substitute MyPK with the name of your Primary key column(s)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 12:35:31

also note that you can have only 1 instead of trigger per table per statement

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-19 : 14:13:53

A better solution would be to design the table so that this is not necessary.

Create another table that holds only a single row that has the ID of the default profile, and then the only thing you have to do is update one row in one table to set the default.


-- create table to hold id of default profile
create table DEFAULT_PROFILE
(
DEFAULT_PROFILE_ID int not null primary key clustered,
PROFILE_ID int not null
)
GO
-- Limit table DEFAULT_PROFILE to one row.
ALTER TABLE [dbo].[DEFAULT_PROFILE] ADD 
CONSTRAINT [CK_DEFAULT_PROFILE__DEFAULT_PROFILE_ID_EQUAL_ONE]
CHECK ([DEFAULT_PROFILE_ID] = 1)
GO
-- Add FK to verify the PROFILE_ID exists and prevent deletion of default profile
ALTER TABLE [dbo].[DEFAULT_PROFILE] ADD 
CONSTRAINT [FK__DEFAULT_PROFILE__PROFILE] FOREIGN KEY 
([PROFILE_ID]) REFERENCES [dbo].[PROFILE] ([PROFILE_ID])

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources