Help with Trigger - 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)

Help with Trigger

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-12-11 : 05:40:34

I need help with a trigger that will perform an UPDATE to a table when an insert is invoked

e.g.

CREATE TABLE [dbo].[Users_new] (
  [UserIndex] int IDENTITY(1, 1) NOT NULL,
  [UserID] varchar(253) COLLATE Latin1_General_CI_AS NULL,
  [Password] varchar(129) COLLATE Latin1_General_CI_AS NULL,
  [PasswordDate] datetime NULL,
  [StartDate] datetime NULL,
  [UserExpiryDate] datetime NULL,
  [ManualExpirationDate] datetime NULL,
  [SuspendDate] datetime NULL,
  [UserActive] bit CONSTRAINT [DF__Temporary__UserA__27D8F307_Users_new] DEFAULT -1 NOT NULL,
 
  CONSTRAINT [aaaaaUsers_new_PK] PRIMARY KEY NONCLUSTERED ([UserIndex])
)
ON [PRIMARY]

As you can see above I want the default value of the UserActive column to changed when new a user is added.

What is the correct SIMPLE trigger to use

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 05:49:58

Here your trigger

Create TRIGGER tgr_name ON table_name FOR INSERT
AS
BEGIN

update table_name set UserActive=1 where UserIndex in (select UserIndex from inserted)

End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 06:09:11

I must be missing something....

Why not just make the DEFAULT for [UserActive] to 1 if you want all INSERTS to have 1 in this column?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-12-11 : 06:13:34

quote:
Originally posted by Transact Charlie

I must be missing something....

Why not just make the DEFAULT for [UserActive] to 1 if you want all INSERTS to have 1 in this column?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Hmmmmm. Good question. It just that the whole database was developed by someone else but you're right. I just hope that it does not affect any part of the whole application. Thnaks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources