Trigger modification - 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)

Trigger modification

Author

Topic

askmanojjain
Starting Member

10 Posts

Posted - 2007-07-26 : 13:48:03

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:52:30

You need an update trigger and not an insert trigger.

You don't save these, you execute them. Click F5 or hit the green arrow to execute the code. It then puts it into the database.

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

askmanojjain
Starting Member

10 Posts

Posted - 2007-07-31 : 14:29:45

THANKS TKIZER

I wuld like to share one good solution I have got. MS has released a "DataBase Publishing wizard" and it can do what I want.
i.e. transer data from a sql databaseto other or simple create "Views" in any database etc.

I am trying this solution and hope fully it will work fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources