|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger, Identity and error 8102 (URGENT)?I have a trigger where I want to convert at copy some data from one table (alarm) to another tabel Alarmqueue. Using this query update alarm set alarmcount = alarmcount +1 Gives me this error Server: Msg 8102, Level 16, State 1, Procedure trAlarmsUpdate, Line 10 Cannot update identity column 'AlarmEntryId'. This all worked fine before IDENTITY(1, 1) [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , came along Does anyone have a clue how to solve this? The source table looks like this CREATE TABLE [dbo].[Alarm] ( [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , [SiteId] [int] NOT NULL , [DeviceId] [int] NOT NULL , [ObjectId] [int] NOT NULL , ---SNIP--- Destination table looks like this CREATE TABLE [dbo].[AlarmQueue] ( [AlarmEntryId] [int] NOT NULL , [SiteId] [int] NULL , [DeviceId] [int] NULL , [ObjectId] [int] NULL , --- SNIP --- This is what the trigger looks like CREATE TRIGGER trAlarmsUpdate ON Alarm INSTEAD OF UPDATE AS BEGIN DECLARE @rows int DECLARE @err int DECLARE @ErrMsg varchar(200) UPDATE alarm SET AlarmEntryId = i.AlarmEntryId, SiteId = i.SiteId, DeviceId = i.DeviceId, ObjectId = i.ObjectId, ---SNIP--- AlarmCount = i.AlarmCount FROM inserted i WHERE Alarm.AlarmEntryID = i.AlarmEntryID and Alarm.ObjectID=i.ObjectID INSERT INTO AlarmQueue ( AlarmEntryId, SiteId, DeviceId, ObjectId, ---SNIP--- AlarmCount) SELECT AlarmEntryId, SiteId, DeviceId, ObjectId, ---SNIP--- AlarmCount FROM INSERTED END Cheers Henry
Show quote
"Henry" <n**@il.com> wrote in message You should not be updating the key, especially since you're joining on it, news:OUKIdVhuFHA.1560@TK2MSFTNGP09.phx.gbl... > Hi > > I have a trigger where I want to convert at copy some data from one table > (alarm) to another tabel Alarmqueue. > > Using this query > update alarm set alarmcount = alarmcount +1 > > Gives me this error > Server: Msg 8102, Level 16, State 1, Procedure trAlarmsUpdate, Line 10 > Cannot update identity column 'AlarmEntryId'. > > This all worked fine before IDENTITY(1, 1) > [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , > came along > > Does anyone have a clue how to solve this? > > > and so not really changing it. UPDATE alarm SET AlarmEntryId = i.AlarmEntryId, SiteId = i.SiteId, DeviceId = i.DeviceId, ObjectId = i.ObjectId, ---SNIP--- AlarmCount = i.AlarmCount FROM inserted i WHERE Alarm.AlarmEntryID = i.AlarmEntryID and Alarm.ObjectID=i.ObjectID Should be UPDATE alarm SET SiteId = i.SiteId, DeviceId = i.DeviceId, ---SNIP--- AlarmCount = i.AlarmCount FROM inserted i WHERE Alarm.AlarmEntryID = i.AlarmEntryID and Alarm.ObjectID=i.ObjectID David Should be
UPDATE AlarmQueue ... instead of UPDATE alarm ... -- David Portas SQL Server MVP -- Disregard that. David Browne has the right answer.
-- David Portas SQL Server MVP -- Henry,
To my knowledge you cannot update a column that has the IDENITY property. Also, unless you're testing you'll probably want to remove the SELECT statement from your trigger code. HTH Jerry Show quote "Henry" <n**@il.com> wrote in message news:OUKIdVhuFHA.1560@TK2MSFTNGP09.phx.gbl... > Hi > > I have a trigger where I want to convert at copy some data from one table > (alarm) to another tabel Alarmqueue. > > Using this query > update alarm set alarmcount = alarmcount +1 > > Gives me this error > Server: Msg 8102, Level 16, State 1, Procedure trAlarmsUpdate, Line 10 > Cannot update identity column 'AlarmEntryId'. > > This all worked fine before IDENTITY(1, 1) > [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , > came along > > Does anyone have a clue how to solve this? > > > > > The source table looks like this > > CREATE TABLE [dbo].[Alarm] ( > [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , > [SiteId] [int] NOT NULL , > [DeviceId] [int] NOT NULL , > [ObjectId] [int] NOT NULL , > ---SNIP--- > > > Destination table looks like this > CREATE TABLE [dbo].[AlarmQueue] ( > [AlarmEntryId] [int] NOT NULL , > [SiteId] [int] NULL , > [DeviceId] [int] NULL , > [ObjectId] [int] NULL , > --- SNIP --- > > > This is what the trigger looks like > CREATE TRIGGER trAlarmsUpdate > ON Alarm > INSTEAD OF UPDATE > AS > BEGIN > DECLARE @rows int > DECLARE @err int > DECLARE @ErrMsg varchar(200) > > UPDATE alarm > SET > AlarmEntryId = i.AlarmEntryId, > SiteId = i.SiteId, > DeviceId = i.DeviceId, > ObjectId = i.ObjectId, > ---SNIP--- > AlarmCount = i.AlarmCount > FROM inserted i > WHERE Alarm.AlarmEntryID = i.AlarmEntryID and Alarm.ObjectID=i.ObjectID > > > INSERT INTO AlarmQueue > ( AlarmEntryId, > SiteId, > DeviceId, > ObjectId, > ---SNIP--- > AlarmCount) > > SELECT > > AlarmEntryId, > SiteId, > DeviceId, > ObjectId, > ---SNIP--- > AlarmCount > FROM INSERTED > > > END > > > > > Cheers > Henry > > Closer look INSERT...SELECT - OK.
Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:uvnOafhuFHA.3224@TK2MSFTNGP10.phx.gbl... > Henry, > > To my knowledge you cannot update a column that has the IDENITY property. > Also, unless you're testing you'll probably want to remove the SELECT > statement from your trigger code. > > HTH > > Jerry > "Henry" <n**@il.com> wrote in message > news:OUKIdVhuFHA.1560@TK2MSFTNGP09.phx.gbl... >> Hi >> >> I have a trigger where I want to convert at copy some data from one table >> (alarm) to another tabel Alarmqueue. >> >> Using this query >> update alarm set alarmcount = alarmcount +1 >> >> Gives me this error >> Server: Msg 8102, Level 16, State 1, Procedure trAlarmsUpdate, Line 10 >> Cannot update identity column 'AlarmEntryId'. >> >> This all worked fine before IDENTITY(1, 1) >> [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , >> came along >> >> Does anyone have a clue how to solve this? >> >> >> >> >> The source table looks like this >> >> CREATE TABLE [dbo].[Alarm] ( >> [AlarmEntryId] [int] IDENTITY (1, 1) NOT NULL , >> [SiteId] [int] NOT NULL , >> [DeviceId] [int] NOT NULL , >> [ObjectId] [int] NOT NULL , >> ---SNIP--- >> >> >> Destination table looks like this >> CREATE TABLE [dbo].[AlarmQueue] ( >> [AlarmEntryId] [int] NOT NULL , >> [SiteId] [int] NULL , >> [DeviceId] [int] NULL , >> [ObjectId] [int] NULL , >> --- SNIP --- >> >> >> This is what the trigger looks like >> CREATE TRIGGER trAlarmsUpdate >> ON Alarm >> INSTEAD OF UPDATE >> AS >> BEGIN >> DECLARE @rows int >> DECLARE @err int >> DECLARE @ErrMsg varchar(200) >> >> UPDATE alarm >> SET >> AlarmEntryId = i.AlarmEntryId, >> SiteId = i.SiteId, >> DeviceId = i.DeviceId, >> ObjectId = i.ObjectId, >> ---SNIP--- >> AlarmCount = i.AlarmCount >> FROM inserted i >> WHERE Alarm.AlarmEntryID = i.AlarmEntryID and Alarm.ObjectID=i.ObjectID >> >> >> INSERT INTO AlarmQueue >> ( AlarmEntryId, >> SiteId, >> DeviceId, >> ObjectId, >> ---SNIP--- >> AlarmCount) >> >> SELECT >> >> AlarmEntryId, >> SiteId, >> DeviceId, >> ObjectId, >> ---SNIP--- >> AlarmCount >> FROM INSERTED >> >> >> END >> >> >> >> >> Cheers >> Henry >> >> > > |
|||||||||||||||||||||||
Other interesting topics