Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies


ADVERTISEMENT

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Generic Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)

Dec 5, 2006

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.

The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}


//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

View 16 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Oct 30, 2007

Table 1





First_Name

Middle_Name

Surname


John

Ian

Lennon


Mike

Buffalo

Tyson


Tom

Finney

Jones

Table 2




ID

F

M

S

DOB


1

Athony

Harold

Wilson

24/4/67


2

Margaret

Betty

Thathcer

1/1/1808


3

John

Ian

Lennon

2/2/1979


4

Mike

Buffalo

Tyson

3/4/04


5

Tom

Finney

Jones

1/1/2000


I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.

View 17 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Feb 5, 2008

A





ID

Name


1

Joe


2

Fred


3

Ian


4

Bill


B





ID


1


4

I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?

I believe the trigger should look something like this:

create trigger test_trigger
on a
for insert
as
insert into b(ID)

select i.id
from inserted i
where
--specific USER

View 9 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Update Trigger

Apr 6, 2004

Hello All,
I've been adviced without much detail that the best way to handle updates is with an Update Trigger to write to an AuditTable.
I always thought it was better to avoid triggers.
I'm getting confused very fast in reading about triggers.
Could someone please point me in the right direction?

Thank you,
Tina

View 6 Replies View Related

UPDATE Trigger For PK

Dec 2, 2004

i am using address table to store the address of employees, agents, students; so can't use cascade update and delete. so i decided to use trigger but i got stuck here as well.
Here employeeid is the PK of employee id. so when it is updated, i want to update that id in corresponding address table as well.
so what i need is to find out the previous value of EmployeeID before updating to use in WHERE clause and new EmployeeID to use in SET clause.

<code>
CREATE TRIGGER [AddressUpdate] ON [dbo].[MCS_Employee]
FOR UPDATE
AS
IF UPDATE(EmployeeID)
BEGIN
UPDATE MCS_Address SET EmployeeID = 'i am stuck here; how to know the previous value?
WHERE EmployeeID = ???
END
</end>

ironically, PK in my table can be updateable as well.

View 2 Replies View Related

Update Trigger

Sep 1, 2005

Hello all. I have a table which is the exact replication of a different table. Now if an update takes place in the original table I want to copy that row into the new table without the update.I have this but I am not sure if it works.Create Trigger Content_Archive on content
For Update
As
Insert Into content_audit Select * From DeletedI dont know if Updated exists so I am using Deleted. It is returning both rows (before and after update). And the insert should take place as soon as an update takes place in any field in the content table.

View 3 Replies View Related

Trigger Update?

Feb 21, 2001

Hello, I have two tables, looks as follow.

TOPIC
------
TOPIC_ID
STATUS_ID
TOPIC


THREAD
-------
THREAD_ID
TOPIC_ID
STATUS_ID
THREAD
THREAD_DATE
NAME

I want to update STATUS_ID in TOPIC when I post a new THREAD.
I guess the best would be to use a trigger?
Don't know much how you write them so if someone please could help or point me in right direction.
My plan is to always show the updated STATUS_ID in TOPIC while I have history in the THREAD TBL.
I am using MS SQL 7.0

Thanks for a great site.

Linkan

View 2 Replies View Related

I Am Trying Do A Update With A Trigger?

Feb 22, 2001

Hello,

I am new to this and on my way to learning. I hope someone can help me with a trigger. I want to update status of one table when I insert a new threat in another. This is what I have come up with so far.

CREATE TRIGGER [trg_Update_Status] ON tForumThread
FOR INSERT
AS
BEGIN
UPDATE tJournalTopic (STATUS_ID)
SELECT STATUS_ID
FROM Inserted

Thanks,
Linkan

View 2 Replies View Related

Update Trigger

Jan 5, 2000

View 1 Replies View Related

(Update) Trigger Help

Dec 17, 2002

I need to be able to select the row or specific field that was updated in an update trigger. I dont have any time-stamp or before-after value columns to compare. Please help!

View 4 Replies View Related

TRIGGER FOR UPDATE

Aug 20, 2001

I HAVE TWO TABLES IN THE DATABSE T1 AND THE SECOND TABLE(T2) IS FOR AUDITING.
THE STRUCTURE OF BOTH THE TABLES IS SIMILAR WITH ONE MORE COLUMN 'STATUS' IN THE T2.
I HAVE ALREADY WRITTEN THE TRIGGERS FOR THE INSERT AND DELETE TRIGGERS SO THAT I CAN SAVE THE
RECORD WITH STATUS OF 'NEW INSERT' OR 'DELETE' OR 'UPDATE' IN THE AUDIT TABLE(T2).
HELP ME IN WRITING THE TRIGGER FOR UPDATE.
MY PROBLEM IS I DON'T KNOW HOW TO STORE THE UPDATED COLUMNS VALUES INTO VARIABLES AND SO THAT
I CAN STORE THE OLD VALUES INTO THE AUDIT TABLE(T2).
I M USING SQL SERVER 6.5, SO WE CAN NOT USE COLUMNS_UPDATED() IN THE TRIGGER.
PLEASE SUGGEST ME THE CODE SO THAT I CAN STORE THE OLD ROW IN THE TABLE(T1) TO TABLE (T2)
WITH STATUS 'UPDATE' IN THE T2.

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH




=============================
/*test trigger for insert status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_insert') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_insert
GO

CREATE TRIGGER tri_t1_insert ON dbo.t1
FOR INSERT
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from inserted
insert into t2(stamp,name,status) values(@v1,@v2,'NEW INSERT')
end
GO
========================================
/*test trigger for delete status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_delete') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_delete
GO

CREATE TRIGGER tri_t1_delete ON dbo.t1
FOR delete
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from deleted
insert into t2(stamp,name,status) values(@v1,@v2,'DELETE')

end

View 1 Replies View Related

Update Trigger

May 29, 2002

I have an update trigger on a table on my transactional database that inserts a row of data into another database (audit database)for any modification made on the transactional database.
So if i modify a row on tran db it will write the data modified as a new row in the audit db.

This works fine if I am updating only 1 row with each Update statement. However if I update more than 1 row (multiple rows) with the same Update statement, the update trigger only inserts the last row modified in the audit database. So I lose record of any other rows modified with the same update statement.

Is there a way by which i can change my Update trigger or something, so I get all the rows updated by 1 update statement inmy audit database???


Thanks a bunch,
Judy

View 4 Replies View Related

Update Trigger

Apr 9, 2001

Hello All,
How do I create an update trigger that only updates the record that is being updated?

TIA,
Terry

View 1 Replies View Related

Update Trigger Plz Help

Mar 4, 2005

well basically i have a table with 3 columns in a table called TEST like:

TEST1 TEST2 TEST3
------- ------- -------
NULL NULL NULL

these columns can allow nulls. What i whant to do with my trigger is do a after trigger and check after the load if a certain column is NULL place a X instead like a flag but only on the columns that are NULL how would i do this.

plz help

View 2 Replies View Related

Update Trigger - Old Value

Oct 14, 2005

Is there a way I can get the old value of a specific field when using an update trigger?
e.g. I want to use the condition 'If Update(Column) '
in odrer to create a logfile which stores the old and new value of a field.
The new value can be get from a Select from Inserted table.
Is there a way I can get the old value (before update) also??

Regards,
Manolis

View 1 Replies View Related

Update Trigger

Mar 7, 2006

I have created a table with the following columns Jobnumber varchar(20), weight real(4), freightcost money(8), trackingnumber vchar(50), comments varchar(2000) and voidid varchar(3)

I wrote a trigger that updates this data based on the voidid to update the package table as followed:
CREATE TIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
DECLARE @JOBNUMBER CHAR(20)
DECLARE @WEIGHT REAL(4)
DECLARE @FREIGHTCOST MONEY(8)
DECLARE @TRACKINGNUMBER CHAR(25)
DECLARE @SHIPMETHOD CHAR(50)
DECLARE @voidid char(2)



SELECT @JOBNUMBER=JOBNUMBER,
@WEIGHT=WEIGHT,
@FREIGHTCOST=FREIGHTCOST,
@TRACKINGNUMBER=TRACKINGNUMBER,
@SHIPMETHOD=SHIPMETHOD,
@VOIDID=VOIDID
FROM INSERTED



UPDATE PACKAGE
SET PACKAGE.WEIGHT = @WEIGHT,
PACKAGE.FREIGHTCOST = @FREIGHTCOST,
PACKAGE.TRACKINGNUMBER = @TRACKINGNUMBER,
PACKAGE.COMMENTS = @SHIPMETHOD
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'N'



UPDATE PACKAGE
SET PACKAGE.WEIGHT = '',
PACKAGE.TRACKINGNUMBER = '',
PACKAGE.COMMENTS = 'UPS VOID',
PACKAGE.FREIGHTCOST = ''
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'Y'

I am getting the following error see attached.
Any help would be great Thank you!

View 2 Replies View Related

Update Trigger

May 10, 2006

Hallo there,

I'm totally new to writing triggers, but managed to get a trigger to update a specific column (ufINCTcost) on change of another column (ufINCKm)

My problem is that the trigger performs this update on ALL rows, which makes it very slow. How can I get it to only update the column on the row where the change was made ?

My trigger looks as follows:


CREATE TRIGGER updateincidents ON [dbo].[_rtblIncidents]
FOR UPDATE, INSERT
AS
IF UPDATE (ufINCKm)
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCTcost=dbo._rtblIncidents.ufINCKm+dbo._rtblInc idents.ufINCToll+dbo._rtblIncidents.ufINCParking+d bo._rtblIncidents.ufINCFlight+dbo._rtblIncidents.u fINCRental

Hope you can help !!

View 5 Replies View Related

Trigger On Update

Nov 11, 2006

Hi,

I've got 2 tables, EMPLOYEE and STORE

The EMPLOYEE table holds all the basic stuff you would want to know about an employee and it contains a STORE_CODE attribute that points to the same attribute in the STORE table (to keep track of the store they work at). The STORE table has the STORE_CODE attribute, a NUM_EMP attribute that keeps track of the number of employees at that store, and some other information that is of no relevance to the question.

I've written the following TRIGGER to update the NUM_EMP attribute in STORE everytime a row is inserted or deleted from EMPLOYEE. It works fine for inserts and deletes but I am clueless as how to make it work for updates (an EMPLOYEE transfers to another store). I appreciate any feedback and please feel free to tell where I've gone wrong so far.

Thanks!


CREATE TRIGGER [UPDATENUMEMP] ON [EMPLOYEE]
FOR INSERT,UPDATE,DELETE
AS

DECLARE @STORECODE INT
DECLARE @NUMEMP INT

/*DELETE CASE*/
IF (NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE DELETECURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMDELETED
GROUP BY STORE_CODE

OPEN DELETECURSOR

FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP - @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
END
CLOSE DELETECURSOR
DEALLOCATE DELETECURSOR
END

/*INSERT CASE*/
IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE INSERTCURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMINSERTED
GROUP BY STORE_CODE

OPEN INSERTCURSOR

FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP + @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
END
CLOSE INSERTCURSOR
DEALLOCATE INSERTCURSOR
END
GO

View 5 Replies View Related

Update Trigger

Apr 22, 2008

This is something I have limited experience on. I need to create an update trigger after insert on one table that updates a completely different production table. My development server is being re-done and I'm not sure when it will be back on line. I have created some audit tables using triggers but they a simple inserts into a new table.

I believe the trigger statement should look like this, any advice would be appreciated.

Thanks in advance.

CREATE TRIGGER OB$1InsertAudit ON OB$0001
AFTER INSERT
AS
Begin
Update AX$0001
Set AX$0001.receiveddate=getdate(), AX$0001.docreviewstatus=null where Ob$0001.objid=AX$0001.refobjid and OB$0001.recordtype=6 and
OB$0001.descriptor in('Ancillary Services Tracking Form',
'FCE Referral Request',
'Remain At Work',
'Voc Rehab Job Retention')

End

View 5 Replies View Related

Update Trigger

Jan 21, 2004

Hi,

If I have a situation where a particular SQL statement updates three records on a table and there is an Update trigger defined on the table, then how many times will the trigger fire - three times or one time.

Thanks in advance.
Raj

View 5 Replies View Related

Update Trigger

Mar 15, 2004

I have this table:

CREATE TABLE [dbo].[EB_Eprom] (
[EpromID] [int] IDENTITY (1, 1) NOT NULL ,
[Naam_Spel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Versie] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checksum1] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checksum2] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Omschrijving] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_vrijgave] [smalldatetime] NULL ,
[Kabinet] [int] NULL ,
[Merk] [int] NULL ,
[Wet] [int] NULL ,
[Bestand_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bestand_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lastedit] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and i created this trigger:

CREATE trigger CT_EB_EpromBestanden on dbo.EB_Eprom
after update
as
update EB_Eprom
set eb_eprom.bestand_1 = '../data/'+str(b.bestand_1) ,
eb_eprom.bestand_2 = '../data/'+str(b.bestand_2)
from EB_Eprom a join inserted b
on a.EpromID= b.EpromID


the thing that this trigger must do is update the given value with the custom path ../data/

Can someone tell me what i do wrong!!
Thanx already
Cheerz Wimmo

View 12 Replies View Related

Update Trigger

May 27, 2004

hi folks.

Í want to do this:
/***************************************/
CREATE TRIGGER TRGfechaModificacion
ON UsuariosVencimientos
AFTER UPDATE AS
DECLARE @updatedID AS int
BEGIN
--Get the last ID updated
SELECT @updatedID = @@updated --???
-- Updated this record with the actual datetime
UPDATE
UsuariosVencimientos
SET
Fecha_Actualizacion = getDate()
WHERE
id = @updatedID
END
/***************************************/

Resume:

I want to get the 'id' updated so I can used for the update query ;)

thanx

View 2 Replies View Related

Before Update Trigger

Apr 14, 2008

Hi,

I'm trying to write an update trigger on sql 2005 but having a few issues. Basically I have a table with say 10 records in there. When an update occurs on a record and I want the trigger to recognise this and place a flag of X in a column to mark it.

The trigger then copies the record marked with X into another table. In the source table where the flag is marked X it will then update it with a GetDate function.

Can someone advise what I'm doing stupidly wrong. I want a before update function. But I'm getting an error on the Before Syntax. Any advise/help appreciated

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Update_Flag]
ON [MYDB].[dbo].[SOURCETABLE]
BEFORE UPDATE
AS
Update [SOURCETABLE]
Set RecordUpdateFlag = 'X'

Update [SOURCETABLE]
Set RecordUpdateType='U' -- For Record Update

Insert into MYDB.dbo.[xSOURCETABLE]
Select * from [SOURCETABLE]
where RecordUpdateFlag = 'X'
and RecordUpdateType='U'

Update [SOURCETABLE]
Set RecordUpdateFlag= GetDate()

Update [xSOURCETABLE]
Set RecordUpdateFlag= GetDate()

View 5 Replies View Related

Update Trigger

Apr 21, 2008

Hi All,
I have a table with 5 rows that represent our production machines, that table is updated every 5 sec by our production software but not all the rows change values although the update runs on every row every time.
Let me try to give an example:

tbl_Machine:

Id | Name | Prod_Count
1 | Press1 | 3
2 | Press2 | 4
3 | Press3 | 0
4 | Press4 | 0
5 | Press5 | 5

After the update:

Id | Name | Prod_Count
1 | Press1 | 4
2 | Press2 | 5
3 | Press3 | 0
4 | Press4 | 0
5 | Press5 | 5

only machine 1 and machine 2 changed values...

I need a trigger to count my production, so I have created a temp table that writes a line every time the update is done, but the problem is that the update is done on every line not just on the ones that change value.
How do I build a trigger to give me just the line that actualy changes the Prod_Count value?

Thank you all for the help.

View 3 Replies View Related

Update Trigger

Jun 24, 2008

Have a Rebate table which holds 2 types of records.

Code type 1 which is a customer_no and item_no combo
Code Type 3 which is a Customer_type and item_no combo

When an order is entered have a trigger that reads the rebate table and if a match is found write the info that is in the Rebate table to an OrderRebate Table. It is possible there will be code type 1 and 3 that will match a certain customer number. But I want it to check for a code type 1 first, then if code type 1 does not exist then check for code type 3 and use it. So right now if both exist both get written.




ALTER TRIGGER [updateOrderRebatebycusttype] ON [dbo].[oeordlin_sql]
AFTER INSERT
AS

--This insert statement looks for matching records in the Rebate Table
--It looks for customer type and item_no combinations or price Code type 3.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
end



--This insert statement looks for matching records in the Rebate Table
--It looks for customer number and item_no combinations or price Code type 1.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
inserted.item_no = Rebate.item_no and inserted.cus_no = Rebate.cd_tp_1_cus_no
where inserted.item_no = Rebate.item_no and inserted.cus_no = Rebate.cd_tp_1_cus_no
end



--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 3 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),freefield3 = 'Rebate'
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and
inserted.line_seq_no = OrderRebate.line_seq_no
end





--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 1 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = Round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),
freefield3 = 'Rebate'
from inserted INNER JOIN rebate on
inserted.cus_no = Rebate.cd_tp_1_cus_no AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and
inserted.line_seq_no = OrderRebate.line_seq_no
end

View 1 Replies View Related

Update Trigger

Aug 8, 2005

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRIGTEMP1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRIGTEMP1]
GO

CREATE TRIGGER [TRIGTEMP1] ON [dbo].[employee]
FOR UPDATE
AS
declare @a int, @b int, @C int, @d varchar(200), @E int
SELECT @c = COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE'
set @a = 1
while @a < @c + 1
BEGIN
set @b = convert(int,SUBSTRING(COLUMNS_UPDATED(),@a,1))
IF @b > 1
begin
if (@b & 1 = 1)
begin
SET @e = (@a - 1) * 8 + 1

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 2 = 2)
begin
SET @e = (@a - 1) * 8 + 2

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 4 = 4)
begin
SET @e = (@a - 1) * 8 + 3

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 8 = 8)
begin
SET @e = (@a - 1) * 8 + 4

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 16 = 16)
begin
SET @e = (@a - 1) * 8 + 5

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 32 = 32)
begin
SET @e = (@a - 1) * 8 + 6

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 64 = 64)
begin
SET @e = (@a - 1) * 8 + 7

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 128 = 128)
begin
SET @e = (@a - 1) * 8 + 8

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end
end

set @a = @a + 1
end

Kapil Arya

View 3 Replies View Related

Update Trigger

Sep 21, 2005

I am new to sql triggers and tried to create one that updates a date field but what it does is add a new row with the update field.
can someone please help me fix this?

CREATE TRIGGER Tg_Update_close ON [dbo].[tblTicket]
for
UPDATE
AS
insert into tblticket (datecomplete)
select getdate()

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved