SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update Insert Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/30/2013 :  09:18:04  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/30/2013 :  09:54:11  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/30/2013 :  10:20:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/30/2013 :  12:10:14  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/31/2013 :  03:00:44  Show Profile  Reply with Quote
No, status as in the after executing Insert or Update statement I should get the message "Updated" or "Inserted"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000