SQL Server - Trigger inserted change to updated?
Asked By farrukh on 07-Jun-12 02:20 AM
I need to ask i have on trigger on Inserted for example
select @rate = i.VAL21 from inserted;
How can i change it to updated ? i dont find updated ?
Thanks and Regards,
Vikram Singh Saini replied to farrukh on 07-Jun-12 04:33 AM
Whenever you create trigger on tables, the sql server automatically creates two magic tables named as Inserted and Deleted.
As the name suggestes, inserted keeps value which have been inserted in table or updated in table. Deleted keeps information of rows which have been deleted or have been modified during update command.
There is no updated table. Let us know what exactly are you trying to achieve?
However if you have updated some table and want to retrieve the old table values to log or to show user about it. In that case you can use the Deleted table. As I have shared already, that it maintains the modified old values too.
Hope it helps.
farrukh replied to Vikram Singh Saini on 07-Jun-12 07:09 AM
Vikram Singh Saini
Thanks for reply what iam trying to do i have a paramter i.VAL33 i need when ever this column updated then the trigger runs and perform the below calculation it works on insert but not on update?
ALTER TRIGGER [dbo].[TRIGGER] ON [TEST].[dbo].[ITEM_EVENT]
declare @event_type1 varchar(100);
declare @item_type varchar(100);
declare @start_date date;
declare @item_id varchar(100);
declare @Parameter decimal(20,10);
select @event_type1 = i.event_type1 from inserted i;
select @item_type = it.ITEM_TYPE from inserted i, ITEM it where it.ITEM_ID = i.ITEM_ID;
select @start_date = i.START_DATETIME from inserted i;
select @item_id = i.ITEM_ID from inserted i;
select @Parameter = i.VAL33 from inserted i;
if (@event_type1 = 'VOL' and @item_type = 'SAM')
set VAL3 = (
SELECT CASE (EVTR.VAL38) WHEN 0 THEN (NULL)ELSE
CASE (EVTR.VAL38) WHEN NULL then (NULL ) ELSE
CASE (EVTR.VAL11) WHEN 0 THEN(NULL )ELSE
CASE (EVTR.VAL11) WHEN NULL THEN (NULL )ELSE
(EVTR.VAL38)/(EVTR.VAL11 )END END END END
[TEST].[dbo].TABLE_REF EVTR,[TEST].[dbo].ITEM ID
WHERE EVTR.event_type1 ='WEL'
AND EVTR.ITEM_ID =ITEM_EVENT.ITEM_ID
AND EVTR.START_DATETIME=(SELECT MAX(START_DATETIME)
FROM [TEST].[dbo].TABLE_REF EVTR2
WHERE START_DATETIME <=ITEM_EVENT.START_DATETIME
AND EVTR2.ITEM_ID = EVTR.ITEM_ID)
where [TEST].[dbo].ITEM_EVENT.ITEM_ID = @item_id
and [TEST].[dbo].ITEM_EVENT.START_DATETIME = @start_date
and [TEST].[dbo].ITEM_EVENT.event_type1 = @event_type1;
Vikram Singh Saini replied to farrukh on 07-Jun-12 07:31 AM
My Suggestion (give it try...)
(1) Create Update trigger separately on the same table. Don't mix it with After Insert.
(2) Then modify following lines inserted keyword with deleted one as:
select @event_type1 = i.event_type1 from deleted i;
select @item_type = it.ITEM_TYPE from deleted i, ITEM it where it.ITEM_ID = i.ITEM_ID;
select @start_date = i.START_DATETIME from deleted i;
select @item_id = i.ITEM_ID from deleted i;
select @Parameter = i.VAL33 from deleted i;
(3) Finally test it and let us know.
farrukh replied to Vikram Singh Saini on 08-Jun-12 02:18 AM
Thank you very much now the trigger works for update too?
Vikram Singh Saini replied to farrukh on 08-Jun-12 04:30 AM
ALTER TRIGGER Trigger1 ON dbo.emp FOR Insert AS BEGIN DECLARE @f1 bit select @f1 = ( SELECT complete_Cl in my case i want to check that Id which is recently updated before this trigger will got fired. . So what I have to write there????? SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record SELECT @@IDENTITY It
Hello all I tried to create BEFORE UPDATE TRIGGER IN SQL SERVER 2005 but I usually get error message tells me that ther's Syantax error, please
Hello, I'm discussing the scenario. An external application will insert data in a table in sql server. My application(written in c#) have to pull the data immediately from db and process will insert in the table within a second. So I want to use an insert trigger and notify or call my application but don't know how. Will u help me s a couple of links that discusses how to call an external application from a trigger: http: / / www.mcse.ms / archive94-2005-4-1577827.html http: / / www.windowsitpro.com / SQLServer / Article
How to create trigger in sql server 2005 express edition? I am using visual studio environment. I right-clicked on table in
I need to be able to view the contents of a trigger and / or index in SQL Server programmatically (VB 6.0 SP 6) for puposes of comparison. Anyone know how to do this? a sp_helptext TriggerName using ADO and you can get the text of the Trigger for an index oyu can use sp_help sp_helptext works perfectly. Thanks so much! keywords: SQL Server, SQL Server Trigger, trigger, VB description: Viewing a SQL Server Trigger / Index in VB
explain the differences between oracle and sql server SQL Server is MicroSoft Product Oracle is Multinational Company am not asking about the companies, sql server
what Are Magic tables In SQL SERVER There are 2 Magic Tables in SQL server Inserted and Deleted. These are mantained by SQL server for Internal processing whenever an update
Hi , The SQL Server collation had been changed . Ignoring SQL server errror log ; Is there anyway to find when the last time the SQL server collation
Hi Guys, Can any one help me out with writting a SQL trigger that Iam looking to build for one of the database tables.I basically want the sql trigger to be executed if any one of the table rows has been updated with a
Can you suggest me what tutorials or links or ebooks we have to reffer to improve knowledge on sql server as a 1+year experinced person. what i have to reffer as a 1+ experinced dml statement while udf cannot be used for the same. What is the cursor in SQL server? Cursors are used to hold the data temporarily for processing for some logic. It stores