Trigger Hell ... HELP!

Oct 26, 2004

I have two tables:

table1
field1 = identity
field2 ...
field3 ...
field4 ...

table 2
field1 = identity
field2 = foreign key (table1 field1)
field3 ...

When I delete a row in table1, a cascade delete relationship deletes the appropriate table2 row(s). Since I have a trigger on table2 that updates a few fields in table1 (field3, field4), when I try to delete table1, I get an error. The cascade delete tries to fire off the trigger in table2, which in turn tries to update table1 fields and thus fails. How do I circumvent the triggers from firing?

Triggers look something like this:

CREATE TRIGGER trg_delete_table1_field3_field4
ON dbo.table2
FOR DELETE
AS
BEGIN
DECLARE @newField3Value as money
DECLARE @newField4Value as money
Set @newField3Value = (SELECT SUM(field3) FROM table2 WHERE key = (SELECT key FROM deleted))
Set @newField4Value = (SELECT SUM(field4) FROM table2 WHERE key = (SELECT key FROM deleted))
UPDATE table1
SET field3 = @newField3Value ,
field4 = @newField4Value
WHERE key IN (SELECT key FROM deleted)
END

View 4 Replies


ADVERTISEMENT

Type Mismatch...hell Oh Hell

Apr 14, 2004

hi all,

ive been having this problem recently and havnt been able to hunt down a solution for it....

i have a table. 3 columns. name(varchar),age(int) and job(varchar)

i have a stored procedure which takes in name age and job from a form and attemps to through them into the db,but its giving me...

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

or
Type Mismatch..
lovely!

Here's some code for the sproc

CREATE proc sp_insertinfo
@name varchar(50),
@age int,
@job varchar(50)

as

insert into users
values (@name,@age,@job)

GO


and for the actual ASP file...



strConn="Provider=SQLOLEDB;User ID=sa; Password=xxxxx; Initial Catalog=Skills; Data Source=xxxxx"
oConn.Open strConn
Set oCmd.ActiveConnection = oConn

'assign form info to params

name=Request.Form("name")
age=Request.Form("age")
job=Request.Form("job")



'call sproc

oCmd.CommandText = "call sp_insertinfo @name,@age,@job"

'Append params


oCmd.Parameters.Append oCmd.CreateParameter("name", adVarChar, adParamInput, 50, name)
oCmd.Parameters.Append oCmd.CreateParameter("age", adInteger, adParamInput,4,age)
oCmd.Parameters.Append oCmd.CreateParameter("job", adVarChar, adParamInput, 50, job)

'execute the sproc with the params

Set oRs = oCmd.Execute



Anyways it would be great if you could check that out there. Any help def appreciated as this "simple" prob is holding me up big style!

Cheers!
damalo

View 9 Replies View Related

I Think I May Be In Hell?!

Jul 6, 2007

So here's the deal. I just started with a new company as a SQL Developer/Analyst. I've got a couple years of experience with SQL Server, mostly 2000, on some fairly large and complex databases (or so I thought).

So I get to this new company and the database structure is just wacky. I've never seen anything like this before. After a few google searches I find a bunch of articles on EAV. Yup, that's what I've gotten myself into. On top of that, it seems to be some exploded EAV hybrid, possibly EAV/CR or something I saw. I've dubbed it the ESEASAADSADAVSAVCRS Schema (Entity Subentity Attribute subattribute attributedata subattributedata attributevalue subattributevalue circular reference system). Gotta laugh so you don't cry, right?

As far as I can tell, all of the data they have is submitted from clients, cleaned, aggregated and then used to generate reports which clients in turn subscribe to. From what I've read and seen here, EAV is horrible for reporting (among other things) and they are having performance problems. The guy 2 times before me made a reporting table structure that does all of the aggregations and spits the data into new tables for the reports to run off of. The problem? The reporting table structure is also EAV!

As the original designer left the company, he said the word CUBE. Then comes in the next "SQL Guru" (she was only here for 6 months, can't imagine why). So they start doing upgrades to SQL 2005 and she takes her predecessors advice and starts designing a dimensional model in SSAS based of the reporting database structure. EAV + CUBES = WTF? Did I happen to mention she didn't have any OLAP experience when she started? (neither do I, at least not in a production environment)

So now there's me sitting here 2 weeks in with an EAV database, a pseudo-EAV reporting database, some unfinished cubes, not wanting to touch anything for fear of the whole thing imploding.

Here are some of the factors I must take into account:
1. The company website serves as the client UI and is tightly integrated with the EAV schema.
2. New data sources come and go quite often which means lots of attribute changes to the data
3. After looking at the data with what little SSAS knowledge I have, it seems that going this direction might just be useless. I believe all of the fact/measure data is stored in all of these dynamic attributes and it seems like I would be changing them on every load.
4. I thought about dropping the cube idea and redesigning the reporting database structure to 3NF and then pumping the data from EAV to 3NF for reporting but... uhhh... damn, my mind went blank
4. My brain is fried from looking at this thing so I can't remember what other points I was going to bring up... please give some advice.

View 17 Replies View Related

Query From Hell

Sep 13, 2007

if when the data is in mdb format the below query worksSELECT *FROM [rating & px Tgt History]WHERE ((([from] Like "*Init*" And [action] Like "*Target*")=False and deleted=false));but when the access linked to backend is sql server via odbc  i get thisODBC call failed{microsoft][odbc sql server driver][sql server]line 1:incorrect syntax near '=' #170
 

View 1 Replies View Related

SQL Hell....little Help Would Be Great!

Jan 4, 2005

hi, im currently on sql hell right now. im having a hard time learning this sql thingie....

...the thing is this: im currently using the book ASP.NET Unleashed and most of the examples there are on SQL. what i was trying to do before was convert everything to OleDb to fit the ms access which i have right now.

unfortunately, some of the codes seem not to work properly. maybe its because of im using OleDb...

so what i did was i downloaded the MSDE sp3 package and installed it on my PC. now that i have an sql server for my WebMatrix, i just dont know what to do next? i mean, where do i put the sql sample databases like northwind and pubs???

im really confused about this sql thing. i really hate it.

help!!!

View 1 Replies View Related

Replication Hell!!

Oct 15, 2004

All,

Is there a way to hardcode the ip address instead of the Server name in replication script? One of our server is registering 2 IP addresses for itself in DNS. One valid and one invalid. Which is causing the replication to fail.

exec sp_addsubscription @publication = N'WorkFlowtoStats_Weekly', @article = N'all', @subscriber = N'IMGSTAT01', @destination_db = N'Stats', @sync_type = N'automatic', @update_mode = N'read only', @offloadagent = 0, @dts_package_location = N'distributor'
GO

Thanks

Lystra

View 1 Replies View Related

To Hell In A Handbasket

Jul 9, 2007

I have several smallish databases running on an MPC (www.mpccorp.com) server. Device Manager says it has an LSI Logic 1020/1030 Ultra320 SCSI Adapter and a MegaRAID SATA 150-6 RAID controller. It doesn't have any kind of Windows-accessible RAID management interface.

Several months ago I started getting corrupt databases. They would get errors that a DBCC CHECKDB couldn't fix. I never found specific help on this but most of the similar issues I saw pointed toward the RAID controller. We contacted the MPC, who had updated RAID firmware for us to try. We flashed the RAID card reformatted the disks, and restored everything from the last good backup (it had been throwing errors for a couple weeks before I noticed them).

All was good for about a month, but now I'm back to the same situation. I have several corrupt databases. I have good backups, but can't even restore them because I get errors on the restore. My next step is to pay for an incident with Microsoft, but I suspect they'll just point me back to the hardware. If you have any suggestions for problem determination or resolution, I'd sure appreciate them!

Cheers,
Martin Nickel

Sample corruption error:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x3f380c2c). It occurred during a read of page (1:9) in database ID 9 at offset 0x00000000012000 in file 'E:Program FilesMicrosoft SQL ServerMSSQLDataMyDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.

Sample error during DBCC CHECKDB:
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9156028125792763904 (type Unknown), page (34262:2139451659). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
Repairing this error requires other errors to be corrected first.

Sample database restore error:
Msg 3283, Level 16, State 1, Line 1
The file "MyDB_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

View 2 Replies View Related

My Stored Procedure Hell!

May 22, 2007

Ok, I posted here recently and received helpful replies which allowed me to work around a problem.  The original question was posted here:http://forums.asp.net/t/1112669.aspxBut because I'm learning both asp.net 2.0 AND vb 2005 I sort of want to get to the bottom of stuff.  I've found out what was going wrong, but I don't understand it.The problem related to retrieving an output parameter to a stored procedure.  I was adding the parameter to the command object I was using as follows: cmd.Parameters.Add(New SqlParameter("@memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output)) but it wasn't working (ie I wasn't seeing the return value).   A helpful poster's work around was to instead do this: Dim pMemberId As New SqlParameter("@memberid", SqlDbType.Int)pMemberId.Direction = ParameterDirection.Outputcmd.Parameters.Add(pMemberId)  Having poked around some more, I've discovered that if I use the original code and then type:?cmd.Parameters("@memberid").Direction I get the value:Input {1}This even happens if I explicitly use 2 instead of Data.ParameterDirection.Output Can anyone explain why this is happening?  What's the point of allowing me to pass a parameter into a constructor if it's just going to ignore it?  

View 8 Replies View Related

Text Qualifier Hell

Oct 16, 2004

Hi all,

I'm trying to use DTS to import a space delimited file. One column uses " as a text qualifier so I set this in the options. The problem arises when a " shows up between the 2 text qualifiers. It's seen as a set of qualifiers with a 2nd qualifier with no end. I obviously get an error at this point. Anyone have any good advice on how to squash this one?

View 2 Replies View Related

SQL Esxpress Setup Is Slow As Hell

Feb 21, 2007

Hi,

Im installing sql express 2005 and i need only the basic components i.e
addlocal=SQL_Data_Files

But it takes around 30 minutes to install which it much longer then i would expect for
a 30 mb application on download....

it happens on 3.0 GHZ cpu ..with 2 GB ram XPsp2

what could be the problem?
or is it normal that installation is such a long time

View 4 Replies View Related

Data Connections: Where The Hell Are They Stored??!!

Jul 21, 2007

Hello all,



Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!



Does anybody know?



Thanks in advance,



Jerome Smith

View 6 Replies View Related

SQL Server 2005 Beta 2 Uninstall Hell

Jul 11, 2005

   Ok, I have tried everything I can think of, but I am still getting errors to do with SQL server 2005 beta. Since the beta expired on all the VS.net 2005 I thought it would be a good idea to uninstall the lot to save some hard disk space...how wrong I was!!

View 4 Replies View Related

CLR Integration And SQL Express XCOPY Deployment Hell!

Jul 1, 2006

I originally developed my application using SQL 2005 Developer Edition, but want to switch to using an XCOPY deployed DB on SQL Express for deployement.

I have successfully copied the database.mdf/ldf files over to me project, and can connect using Data Source=.SQLExpress and AttachDbFilename=|DataDirectory|[database].mdf attributes.

The question is, how do I enable CLR integration for my C# SP's?

I've tried executing the following in various places:

sp_configure 'clr enabled', 1
go
reconfigure
go

But I obviously haven't hit the spot because I'm getting the following error when VS deploys my SP library:

Error: starting database upload transaction failed.
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Any ideas?

View 1 Replies View Related

10 Hours Of Hell -- Reporting Services Under Vista - Does It Work?

Feb 13, 2008



I have a brand-new Toshiba laptop, running Vista Business, that I installed SQL Express onto. Prior to installation, I was sure to install all the requisite IIS components so SSRS would install.

The installation ran fine -- installed all components. The configuration ran fine. Everything that is supposed to be green shows green

But, when I go to http://localhost/ReportServer, I get:




Server Error in Application "Default Web Site/ReportServer"

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

HTTP Error 404.2 - Not Found
Description: The page you are requesting cannot be served because of the ISAPI and CGI Restriction list settings on the Web server.

Error Code: 0x800704ec

Notification: ExecuteRequestHandler

Module: IsapiModule

Requested URL: http://localhost:80/ReportServer

Physical Path: C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServer

Logon User: Anonymous

Logon Method: Anonymous

Handler: AboMapperCustom-34881

Most likely causes:

No handler mapping for this request was found. A feature may have to be installed.
The Web service extension for the requested resource is not enabled on the server.
The mapping for the extension points to the incorrect location.
The extension was misspelled in the browser or the Web server.
What you can try:

Install the feature that handles this request. For example, if you get this error for an .ASPX page, you may have to install ASP.NET via IIS setup.
Verify that the Web service extension requested is enabled on the server.
Open the IIS Manager and navigate to the server level.
In the Features view, double-click ISAPI and CGI Restrictions to verify that the Web service extension is set to Allowed.
If the extension is not in the list, click Add in the Actions pane.
In the Add ISAPI and CGI Restrictions dialog box, type the path of the .dll or .exe file in the ISAPI or CGI Path box, or click Browse to navigate to the location of the file.
In the Description box, type a brief description of the restriction.
(Optional) Check "Allow extension path to execute" to allow the restriction to run automatically. If you do not check this option, the restriction status is Not Allowed, which is the default. You can allow the restriction later by selecting it and clicking Allow on the Actions pane.
Click OK.
NOTE: Make sure that this Web service extension or CGI is needed for your Web server before adding it to the list.
Verify that the location of the extension is correct.
Verify that the URL for the extension is spelled correctly both in the browser and the Web server.
Create a tracing rule to track failed requests for this HTTP status code. For more information about creating a tracing rule for failed requests, click here.
More Information... This error occurs when the necessary Web service extension is not enabled, the location or the name of the extension are misspelled or incorrectly entered.


--------------------------------------------------------------------------------
Server Version Information: Internet Information Services 7.0.


The only lead I could find when I googled this error was a reference to running appcmd to ensure that asp.net was enabled. It sure looks like it is:

C:WindowsSystem32inetsrv>appcmd list config -section:isapiCgiRestriction
<system.webServer>
<security>
<isapiCgiRestriction>
<add path="%windir%system32inetsrvasp.dll" allowed="true" groupId="ASP" description="Active Server Pages" />
<add path="%windir%Microsoft.NETFrameworkv2.0.50727aspnet_isapi.dll" allowed="true" groupId="ASP.NET v2.0.50727" description="ASP.NET v2.0.50727" />
</isapiCgiRestriction>
</security>
</system.webServer>

C:WindowsSystem32inetsrv>


Honestly... I'm out of ideas. I've been messing with this for 8 hours now, and I'm ready to fling the laptop out the window. I've completely UNinstalled SQL Server, IIS, reinstalled both, repeated the uninstall/reinstall after double-checking all files were deleted, and so on.

Does *anyone* know how to resolve this error? I checked IIS.NET and although they have a few references to it (not within the Reporting Services context) there never seems to be a definitive answer as to what the solution is.

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

How To Create New CLR Trigger From Existing T-Sql Trigger

Mar 18, 2008

how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

View 3 Replies View Related

Modifing The Row That Invokes A Trigger From Within That Trigger

Jul 23, 2005

When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.

View 4 Replies View Related

Disabilitazione Trigger [DISABLE TRIGGER]

Jul 20, 2005

Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.

View 4 Replies View Related

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

Drop Trigger With A Variable Trigger Name

Sep 20, 2007



Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger

I have the following Trigger

the error is at

DROP TRIGGER @DeleteTrigger

I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?

thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @DeleteTrigger as nvarchar(max)

select @DeleteTrigger = TableName FROM DELETED



IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @DeleteTrigger

GO

END

GO

View 7 Replies View Related

Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null

Jan 23, 2008



Hi,

I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out

and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up

Any thoughts?


Here is my t-sql


Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)

select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d

where i.req_guid = d.req_guid

and i.req_fname <> d.req_fname



Thanks,
leo

View 7 Replies View Related

Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data

Mar 3, 2008

I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.

If insert data one by one to a table trigger fires success.
Please Help me.

View 1 Replies View Related

Sp Name In Trigger

Jun 17, 2006

There are two different Stored Procedures insert data into a table, is it possible to catch what stored procedure name was that trigged the trigger in the trigger?

View 1 Replies View Related

SQL Trigger .. Please Help...

Jul 26, 2006

Hi, I am trying to script a trigger on a table to accomplish the following... If a row is updated then the value in the 'date' column in that specific row would update to getDate().
I have never created a trigger before and finding it very difficult. Thank you very much in advance.
Kind Regards,
Sean.

View 6 Replies View Related

Trigger Ignored

Feb 4, 2007

Hello
I am having difficulty try to get a trigger to work.
I need to create a trigger querie that will insert a row from TableMain (Column LNames) table to TableA (Column LNames)
Below is my querie
 
CREATE TRIGGER [trig_addTable_A]ON Table_A FOR  INSERT     ASINSERT LName (LName)
SELECT LNameFROM TableMain inserted
 I have inserted test names into the main table but nothing in table A has inserted.  What have I done wrong.
Thanks
 
 

View 6 Replies View Related

SQL Trigger

Mar 11, 2007

I am looking for a INSERT trigger example, to achieve the following objective:
 I would like the trigger to be fired when a row gets inserted into a table. I want to retrive some value from the inserted row and use this value to insert a new row in table 2.
 Thanks
 
chri
 

View 4 Replies View Related

About Trigger

Mar 17, 2007

is it possible to run a trigger forever... whose work is to archive data from a table. or  how can i do the operation to archive automatically ?Regards,Kamrul Hassan 

View 1 Replies View Related

For Each Row Trigger

Aug 14, 2007

Hi,
Does anybody know how could I define a for each row trigger in sql Server 2005?
What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.
"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.
In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.
Any suggestion?.
 
Thanks in advance.
Cheers.

View 8 Replies View Related

Trigger && Asp.net

Aug 28, 2007

Hi everybody
I work on a Project that has Inbox .
I need that if a record has inserted in inbox system alert to user that view it's inbox .
The suitable  solution for it seems trigger ,but i don't how trigger could communicate with Application.
Thanks for any guides.
 Best Regard
A.Doroudian

View 2 Replies View Related







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