INSTEAD OF UPDATE - 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 2008 Forums

Transact-SQL (2008)

INSTEAD OF UPDATE

Author

Topic

Björn
Starting Member

4 Posts

Posted - 2011-10-18 : 07:12:27

Hi,
In my trigger I examine if a row exist and if it does not I will insert it.
But I cant make it work
Gratefull for any tip!!

As folow--

CREATE TRIGGER setIncrementIDnrOnUpdate ON dbo.q_insertCustomFields
INSTEAD OF UPDATE,INSERT
AS BEGIN
DECLARE @ContactID int
IF NOT EXISTS (SELECT ContactID FROM dbo.customfields
WHERE Record = (SELECT Record FROM Inserted) AND
CustomFieldLabels = (SELECT CustomFieldLabels FROM Inserted))
BEGIN
INSERT INTO customfields
( ContactID,
Record,
FTable,
CustomFieldLabels,
Data,
StartDate,
EndDate
)
SELECT
(
SELECT MAX(dbo.customfields.ContactID) from dbo.customfields)+1,
Record, --Ex 101--
FTable, --Ex Person
CustomFieldLabels, --Ex 6
Data, --Ex 333-333-3333
StartDate, --Ex 2011-07-11 14:00:42.0000000
EndDate --Ex NULL
FROM
Inserted
END
ELSE
SET @ContactID = (
(SELECT ContactID FROM dbo.customfields
WHERE Record = (SELECT Record FROM Inserted) AND
CustomFieldLabels = (SELECT customfieldlabels FROM Inserted))
)
UPDATE [HRAB].[dbo].[customfields]
SET
[Data] = (SELECT Data FROM Inserted)
,[StartDate] = (SELECT StartDate FROM Inserted)
,[EndDate] = (SELECT EndDate FROM Inserted)
WHERE @ContactID = (SELECT ContactID FROM Inserted)
END
GO
GO

BjO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-18 : 07:35:30

What is the problem? Do you get an error?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 07:38:36

why do you need a trigger for that? isnt it enough to declare ContactID of identity type and then have a simple insert/update procedure for doing the above DML operations. If you want combine them you can even use MERGE statement in 2008. using Instead of trigger for getting incremental id does not seem to be a good method for me at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Björn
Starting Member

4 Posts

Posted - 2011-10-18 : 08:21:16

Ok, some background...
I am not alowed to alter the base tables becouse there is another system using it.
There is a person table that holds referens to this customFields table but I never know if there is a row or not.
So I need to update person and its refereses nomather if there are rows or not.
I dont need the icremntel id to access my rows but if I add a row then I have to add it becouse of the ofter system.
The trigger works if I firs do an insert and then an update.
But I want to fire the update on my view with person and customFields data and do both update on person, insert on customfields or update on customfields if it exists.

And well yes I am not up to date with a normal prosedure of how to do it so... any Idea?

quote:
Originally posted by nigelrivett

What is the problem? Do you get an error?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BjO

Björn
Starting Member

4 Posts

Posted - 2011-10-18 : 08:23:08

Thanks!
I will look in to what I can do with Merge.

quote:
Originally posted by visakh16

why do you need a trigger for that? isnt it enough to declare ContactID of identity type and then have a simple insert/update procedure for doing the above DML operations. If you want combine them you can even use MERGE statement in 2008. using Instead of trigger for getting incremental id does not seem to be a good method for me at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BjO

Björn
Starting Member

4 Posts

Posted - 2011-10-18 : 08:44:59

Hi again
the error is.. It does not do the insert on update.

BjO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources