Modify 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

Express Edition and Compact Edition (2005)

Modify Trigger

Author

Topic

askmanojjain
Starting Member

10 Posts

Posted - 2007-07-26 : 13:51:21

I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.

Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links

I want to modify trigger so that it generates new number only when it is not supplied in a insert command.

Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT IntAcControlCode FROM inserted)
UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-26 : 13:56:02

Duplicate, locking thread.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources