Update Insert 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 2012 Forums

Transact-SQL (2012)

Update Insert Trigger

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-30 : 09:18:04

I have this Update Trigger:

CREATE TRIGGER trgUpdateInsert
ON [dbo].[Direct_Test]
FOR UPDATE
AS
declare @DirectID int;
declare @DirectName varchar(100);
declare @DirectAmt decimal(10,2);

select @DirectID = i.Direct_ID from inserted i;
select @DirectName = i.Direct_Name from inserted i;
select @DirectAmt = i.Direct_Amt from inserted i;

if update(Direct_Name)
PRINT 'Direct_Name UPDATED'
if update(Direct_Amt)
PRINT 'Direct_Amt UPDATED'
GO

Can I change this trigger to update a record as it does, but if the record doesn't exist then to create one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 09:54:11

how do you update a record if doesnt exist? without update taking place trigger wont be called at all.
So whatever you're asking for makes no sense to me
As a sidenote the above trigger logic is not correct. You cant store values in variables like this from inserted table. It may contain more than 1 record (batch updates) so you should be using table variable instead to store intermediate values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-30 : 10:20:18

I want to create a database object that when a record or records are added or altered on a table, If a record exist I should have updated status "Record Updated" otherwise the record should be created

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 12:10:14

quote:
Originally posted by stahorse

I want to create a database object that when a record or records are added or altered on a table, If a record exist I should have updated status "Record Updated" otherwise the record should be created

updated status where? is it in same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-31 : 03:00:44

No, status as in the after executing Insert or Update statement I should get the message "Updated" or "Inserted"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources