SQL Server Trigger Query

Aug 24, 2007

I have a trigger written below to update another table when one table is updated.
Basically it works when i update row after row from enterprise manager However when i use a script to update 10 or 20 rows then this trigger does not work. Can any one offer any suggestions on where this issue could be ?


DECLARE @iCount INT
DECLARE @OldValue VARCHAR(255)
DECLARE @NEWVALUE VARCHAR(255)
DECLARE @EQNAME VARCHAR(255)
DECLARE @EQTYPE VARCHAR(255)
DECLARE @TEST VARCHAR(255)
SET @NEWVALUE = 'INIT'
IF (UPDATE(VALUE))
BEGIN
SELECT @OldValue = deleted.value from deleted
SELECT @NEWVALUE = inserted.VALUE,@EQNAME = inserted.EQ_NAME FROM inserted where inserted.attr = 'AreaCfg'
IF (@@ROWCOUNT =0)
BEGIN
RETURN
END
IF ((@NEWVALUE = '') OR (@NEWVALUE is NULL))
BEGIN
DELETE [TABLE-1] WHERE ENTITYID = @EQNAME
RETURN
END
IF ((@NEWVALUE != @OldValue))
BEGIN
SELECT @iCount = Count(*) FROM [TABLE-1] WHERE ENTITYID = @EQNAME
IF (@iCount = 0)
BEGIN
-- No records are available so insert
SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME
INSERT INTO [TABLE-1] (AlarmGroupId, EntityID, EntityType, OwnerId) VALUES (@NEWVALUE,@EQNAME,@EQTYPE,'Production Tracker')
END
IF (@iCount > 0)
BEGIN
-- No records are available so insert
SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME
UPDATE [TABLE-1] SET ALARMGROUPID = @NEWVALUE WHERE ENTITYID = @EQNAME
END
END
END

View 3 Replies


ADVERTISEMENT

Running Query In Linked Server Through Trigger

Aug 2, 2006

i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.

View 1 Replies View Related

SQL Server 2005 Query/trigger/function (whatever It Is That I Need)

Dec 8, 2006

Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
 table 1 = stock table
 table 2 = Purchase order table
 in table 2 there are line items that have ordered quantities for parts that we have ordered
 in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1
 
quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this
 
table 2
poNumber             partNumber                 quantity
1                             516                             1
2                             516                             12
3                             516                             4
 
table 1
partNumber            inStock              onOrder
516                          0                        17(this is what i am trying to figure out how to compute)
 
any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.

View 3 Replies View Related

Server Trigger Timing Out Access Append Query

Oct 17, 2013

I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.

The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.

I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.

I have tested the trigger to do a simple task, and this works.

I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables

When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.

I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!

View 10 Replies View Related

Trigger Query

Mar 12, 2008



Hi,
I have a table by the name EmployeeDetails which has Empid, Empname and Date of birth. The empid column is set to auto increment.
Now I want to create a trigger in such a way that, whenever a new employee is added to that table it will display the empid of the newly added emloyee. I don't know how to do that.

Please help?????

View 9 Replies View Related

Getting A Constraint Value From Query In A Trigger?

Mar 1, 2005

say i have a query like:

UPDATE table SET status = 1 WHERE id = 1000

if i have a trigger on that table, is there anyway i can get the id ?

i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?

thanks

View 4 Replies View Related

Query Trigger Email To User

Aug 6, 2013

I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.

Not sure how to query it in SQL.

Due Date = NeedByDate
user = AssignedTo

View 1 Replies View Related

Fetch Query That Triggered The TRIGGER

Dec 26, 2006

HelloThe problem is need to find out the querry that has updated or insertedinto the table and in turn 'Triggered the Trigger'. I have the username, the machine name, Application name, but not the query. The updateis not desired and the application is doing it but the applicationbeing so large we are unable to pin-point the code which is doing thedammage.Pls help!RegardsAnubhav

View 1 Replies View Related

Too Busy To Implement A Trigger? Nor A Conditional Update Query?

Dec 20, 2004

Hi:

In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.

then I try to run a query as follows:

if exists (select rfABC.* A
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L
on A.Address = L.address and
A.metro <> L.metro
begin
print ' ---- Yes metroID <> LAMetro, start job exec.... -----'

insert into tempCatchMetroIDGPRS
select rfABC.*, metro, getdate()
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro

update rfABC A
set A.metro = L.metro
from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro
end
else
begin
print ' ---- no metroID <> LAMetro, skip job exec.... -----'
end

------------------------------
this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...

thanks
David

View 4 Replies View Related

Automatically Trigger A Sum From One Table To Another Upon Update/insert Query

Jul 12, 2005

I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID

I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons

I know this is possible in access, so i'm wondering how to go about it in ms-sql?

any ideas?

View 1 Replies View Related

Proc With Dynamic Query Hang When Insert Trigger Fires....

Feb 16, 2005

Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:

1. when ran at a MSDE server
MSDTC on server is unavailable.

2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.

Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?

thanks
David

View 4 Replies View Related

Error :Execute Trigger From Remote Server To Another Server By Linked Server

Jul 22, 2007

i did "Linked server" between To Servers , and it's Working.

---------------------------



For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2



and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS


BEGIN





SET NOCOUNT ON;


insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND

**************************************************



result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.





how i can execute this trigger



View 5 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

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

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

SQL Server 2014 :: Synchronize Table On Remote Server Via Update Trigger Failing

Jul 21, 2015

We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).

Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.

CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)

[Code] ....

However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing

OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.

Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?

View 3 Replies View Related

DB Engine :: Trigger Jobs On Remote Server To Do Its Work On Original Server

May 10, 2015

I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.

View 4 Replies View Related

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

Sep 8, 2015

I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:

create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end

I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.

View 8 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

Trigger In Sql Server

Nov 27, 2006

In my project I have three table 2 are Master detail type and other is parameter table.tablleA, tableB, tableC.  I want to create a trigger at the time of inserting data in table a, I want to Insert records in   table B according to Primary keyy of tableA and Data using from tableC also.  

View 3 Replies View Related

Sql Server ---- About Trigger

Jan 10, 2007

Dear Advance,
 How can i use trigger to get the latest added value of one table and insert it into another table.
 Regards,
Kamrul Hassan

View 6 Replies View Related

SQL Server Trigger

Nov 20, 2003

I created a Insert trigger which is attached to a table(say tblUser) on SQL Server. After the trigger is fired, the newly inserted data is in the virtual table(Insert) which was created by the trigger. What I like to do is to modify the data in the virtual table(Insert), then put the changed data into the table(tblUser). Is there any way to do that?

Thanks!

Regards,

Kevin Jin

Kevin.jin@itt.com

View 2 Replies View Related

SQL Server Trigger

May 15, 2006

I want to create a trigger in ms sql server 2005.What I want it to do:I create a new user in the aspnet_users table. When this user is inserted I want to automatically insert, username, userid and createdate in the tblUserData table.Can anyone provide me with a clear example on how to do this?Thanks!

View 4 Replies View Related

SQL Server Trigger Help!

May 10, 2001

hi!
This trigger i made does not execute..........can anybody help?


"Create TRIGGER update_status
on book_issue
for insert
as
update book_catalogue
SET status='I' where book_catalogue.code = book_issue.code;
go"

I basically want the status of the book to change to 'I' in the book_catalogue whenever an entry is made in the book_issue.
tables:
book_catalogue
book_issue

P.S the problem realy is in the last sentence i.e, .........where book_catalogue.code=book_issue.code

View 2 Replies View Related

Trigger In SQL Server

Nov 20, 1999

I'm making an app with VB6.0 and SQL Server 6.5, and I have this problem:

I have a table called “comerciales” in which the field “comercial” is primary key.
I have another table called “clientes” in which the field “comercial” is foreign key to
table “comerciales”.

I want to make a trigger that update to NULL the field “comercial” of table “clientes” when I
delete the same value of field “comercial” in table “comerciales”. That is, if I delete the
comercial “Jhon Winter” in table “comerciales” I want that the records of table “clientes”
with comercial = "Jhon Winter" update that field to NULL

I have this trigger, but it gives an error when I execute. Error: “error near delete”

CREATE TRIGGER trg_comerciales ON dbo.comerciales
FOR DELETE


BEGIN
UPDATE clientes
SET clientes.comercial = NULL
FROM deleted
WHERE clientes.comercial = deleted.comercial
END

What is wrong?. Any idea?. Can anybody help me?. Another way of doing the same?
I don't know much about triggers any advice about them, will bw greatly apreciatted.

Thank you very much.

View 1 Replies View Related

SQL Server Trigger

Feb 4, 2004

Hi All,

I am trying to bypass processing in an INSERT SQL Server Trigger when a row is added into a table by a specific stored procedure. Is there anyway before hand in SQL Server to skip the processing inside a trigger whenever it is called by a specific process or is there a way to get the stored procedure id that is inserting a row in the triggering table before processing anything.

Need help badly.

Thanks.

View 3 Replies View Related

Trigger In SQL Server

May 3, 2004

Hi,

Can any tell me how to translate a a trigger from oracle to sql server. For example i have an Oracle trigger which is FOR EACH ROW...

if :new.db_contract is not null then
db_id := :new.db_contract;
cqe:= :new.cqe_numb;
pc:= :new.pc_code;
item := :new.item_no;
fiyr := :new.fy_item;
else
db_id := :old.db_contract;
cqe:= :old.cqe_numb;
pc:= :old.pc_code;
item := :old.item_no;
fiyr:= :old.fy_item;
end if

How shall I translate this in SQL SERVER i.e. Old and New. and how i implement a For Each Row trigger in SQL Server

View 6 Replies View Related

Trigger So On Another Server

Apr 11, 2008

i want to be able to trigger a sp on another server from within an sp. I dont want to do this via a linked server as this will hold up the job while it waits for a response to say the sp has completed. Is there another way i can do this? i have a number of them and i did think of setting up a dts on the other server for each one of them and then executing the dts packages instead.

any ideas anoyone?

View 6 Replies View Related

Constraint/trigger In Sql Server In Asp.net

Nov 24, 2006

In my Projecti want to check the date at the time of insert in A-Tablethat it should be Greater than (>)  Date Defined in B-TableNote:-B-table have only one record  so plz tell me how can i do using Sql-Server Backend only 

View 3 Replies View Related

Trigger Admin For Sql Server

Apr 8, 2005

I am using a lot of triggers to link couple of systems.Is there a way to administrate triggers? Is there a tool I can install to do that?I need to be able to list all the triggers in the database, search them, enable/disable.
Thanks,Igor

View 2 Replies View Related

Trigger And SQL Server Insert

Nov 6, 2005

I'm trying to insert a record into a table and I'm finding that I can only insert the primary key.  When I try to insert only another field only it does not happen which makes me think the primary key is not being auto-created and I think that I have to set up a trigger to do thisExample:<CODE>      oConn.ExecuteMySQL("INSERT INTO tblUsers(strUserID,strName)VALUES(45,'x')", True)</CODE>.The code above works well, but I want to auto-generate a trigger that will insert a unique strUserID (primary Key) each time a user does an insert so that I dont have to specifically indicate strUserID.<CODE>      oConn.ExecuteMySQL("INSERT INTO tblUsers(strName)VALUES('x')", True)</CODE>The code above does not work well because it does not create a strUserID.  How do I create a trigger that will auto-insert a primary key into strUserID?
Thanks.Joe

View 3 Replies View Related







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