Trigger And Any Number Of Updates

Jan 12, 2004

I've a system of users and let's call em subusers. Every User becomes an automatic generated login when entered into the database. Every subuser has a reference to his user and no login, cause only thr root in the chain is able to login. But when the user gets deleted, all subusers become a new user. I've done this with a trigger changing the superUser Value=0:

create trigger abc on Users AFTER DELETE
as
declare @h int
SELECT @h = id FOM inserted
UPDATE Users SET superUser=0 WHERE superUser=@h

Furthermore the trigger deletes all additionally data of the user.
Since every subuser of the deleted user becomes a user himself for every subuser a new Login must be created. I'm using an update triger for this task:

1: create trigger userUpdate on Users After Update
2: AS
3: DECLARE @superold int
4: @supernew int
5: @name nvarchar(55)
6: @date smalldatetime
7: if UPDATE(superUser)
8: begin
9: SELECT @superold= superUser FROM deleted
10: SELECT @supernew=superUser FROM inserted
11:
12: if @superold <> @supernew
13: begin
14: if @superold = 0
15: begin
16: DELETE FROM UserLogin WHERE id=@superold
17: end
18: else if @supernew=0
19: begin
20: SELECT @name=Name,@date=Date from inserted
21: execute createLogin @supernew,@name,@date
21: end
22: end
23: end

The problem is in line 20 and 21, cause the values @name and @date containing only the last updated user(the last entry in the inserted table) thus only for the last user a new Login is created whereas the others become the state user but no login was created. What i need is a method to loop over all entrys in the tables inserted btw. deleted.
Does anybody know how to achieve this, looping the tables and executing a stored procedure for every entry?

bye

View 10 Replies


ADVERTISEMENT

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data

Dec 19, 2007

I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:




Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View 4 Replies View Related

Instead Of Trigger And In-Place Updates

Sep 18, 2007



Converting an existing application, I have a table:


create table problem
(

building char(3),
function char(4),
sqft int,
pct dec(5,2)
)

[pct] is a problem because it depends on it's own row and the sum of all other rows for the same building:

pct = sqft / sum(sqft) over building

I want to create a trigger to update the pct column in the database instead of any business layer. But, because it's updating itself I will use an Instead Of trigger that is separate from the table's Insert & Delete triggers.

The table has a primary key defined such that 'In-Place' updates will be used, that was a technique for reducing disk activity way back when and I can find no reference to it in SQL2005 BOL.

My question is, does the 'Inserted' table still exist for 'In-Place' updates? Or more basically, does the In-Place update still exist?

Thanks.

View 3 Replies View Related

Maximum Number Of Updates Within A Transaction

Aug 21, 2006

Good day to all

SQL Server 2000.

I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.

With smaller files, the process finishes without problems.

I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.

I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.

Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?

Thank you in advance for any help

Cecil Ricardo

View 5 Replies View Related

Trigger To Handle Multirow Updates

Mar 2, 2006

I am trying to implement trigger which can handle multirow updates and which is running on replicated table. So I want it never fails as trigger failure brakes replication.

So:

CREATE TRIGGER on_person_update

BEGIN

-- create temp table

-- populate temp table with Inserted values (I do not need Deleted as PK never change)

COMMIT TRAN

-- Am I right that this insures updates on replicated table will never be rollback after this commit?

BEGIN TRAN A

-- Make a checkpoint here to be able to rollback at any time to this point if something wrong inside loop.

SAVE TRAN MyTran

-- Start looping in temp table

-- RUN DML statement to make neccesary changes for each record in temp table

-- Does it make any sense to do this (IF @ERR below)? When I am trying in DML insert string value into integer column it never gets to IF statement - terminates straight away.

-- Reason why I think I need it as this trigger might be called by another trigger and top level trigger will get an error and can make a decision based on this.

IF @ERR <> 0
BEGIN
ROLLBACK TRAN MyTran
RAISERROR('Insert or Update failed in on_person_sls_update trigger with error: %s', 16, 1, @ERR)
RETURN
END

-- End looping temp

-- Do I need here COMMIT TRAN A or trigger will make commit anyway?

END

Why all of this?

Data changed on distributor and arrive to subscriber as a transaction.

We have a trigger on replicated table which will update replicated table in any way but after that it will update another database on subscriber.

This trigger should be able to handle multirow updates.

When this trigger updates another database it runs DML which fires other triggers so they become nested, if I am right. Our trigger should always accept changes from distributor as if it fails replication brakes but after data saved in temp table none or all changes have to be made.

May be I am copmpletely wrong with this template - hope somebody will help.

Thank you,



Igor



View 2 Replies View Related

Problem: Trigger And Multiple Updates To The Table

Apr 21, 2004

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.

View 7 Replies View Related

Logging Batch Updates Without Using A Trigger Or A Cursor

May 5, 2008

Hi,

Does anyone know if there's a way to log batch updates done using SQL queries without using a trigger or a cursor?

Thanks in advance,
Vinod

View 6 Replies View Related

How To Catch Multiple Updates Done To A Table With A Trigger?

Dec 28, 2007

I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?


Following is the table structure:

Customer_Master(custmastercode, customer_company_name,updated_by)

Following is the trigger:


ALTER TRIGGER [TR_UPDATE_CUST]

ON [dbo].[CUSTOMER_MASTER]

AFTER UPDATE

AS

BEGIN



SET NOCOUNT ON;




IF EXISTS (SELECT * FROM inserted)

BEGIN


declare @custcode int

Declare @message varchar(5000)

Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int

Declare @CUSTOMER_COMPANY_NAME varchar(50)

Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '



select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted


Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '


IF(update([CUSTOMER_COMPANY_NAME]))

Begin

select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted

select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted

Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'

Set @message = @message + @custommessage

End


Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'


INSERT INTO [CHANGE_HISTORY]

([CUSTMASTERCODE]

,[CHANGE_DETAILS])

VALUES (@custcode, @message)

END

END

View 7 Replies View Related

SQL Server 2012 :: Trigger For Updates On A Row Using Previous Record Value?

Mar 9, 2015

I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.

View 9 Replies View Related

Phone Number Trigger?

Feb 12, 2002

How would I go about setting up a "mask" to automatically place the hyphen in phone numbers in SQL Server? I.e., the user types in an area code and then a hyphen automatically inserts and the cursor is on the next blank space.
Thanks for the assist.

Mark
Somewhere down here in Texas

View 2 Replies View Related

Trigger & Record Number

Oct 16, 2007

Hello,

I have a table with a field that contains the record number. This field is an identity column that is needed to be compatible with old Clipper programs. I've to ensure continuity in the numbering. So I have to update those numbers each time one or several records are deleted. I was thinking of using a trigger but I'm quite novice in this domain and don't know how to do it. Does anyone have a solution for this problem ?

Thank you in advance.

Regards,

mathmax

View 20 Replies View Related

Use Trigger To Number Each Order Line

Jan 24, 2006

I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderDetails table for OrderID.

I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.

OrderID LineNumber
1 - 1
1 - 2
1 - 3
2 - 1
2 - 2
3 - 1
3 - 2

I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.

Thanking you in advance.

View 3 Replies View Related

Trigger- Get Row Number From Inserted/deleted

Jul 19, 2006

I need to add the row number or record number to the 'inserted' and 'deleted' tables in a trigger.

Among other things, I have tried-

SELECT 1 as rowId, i.* INTO #ins FROM inserted i
if @@ROWCOUNT > 1 --if multiple rows, set row number
begin

SELECT @pkWhere = coalesce(@pkWhere + ' and ', ' where ') + PKF.colName + ' <= i.' + PKF.colName FROM #primaryKeyFields PKF
set @strSQL = 'update #ins set rowId = (Select Count(*) From #ins i' + @pkWhere + ')'

exec (@strSql)

end

-the above sets rowId for every record to the total instead of sequential. Keep in mind that this code is used to create a trigger for any table, hence I cannot specify any column names literally.

This SHOULD be simple... right?

View 17 Replies View Related

How To Represent Maximum Number Of Books Borrowed At Same Time Through A Trigger

Feb 20, 2013

I have to build a database model, create tables and use triggers or/and procedures on it. It's about a library. Books are borrowed by students. They can make a reservation of a book.

I built the database model so far. My problem is how to represent following tasks:

- Students can borrow a maximum of 5 books at the same time.
- bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first

I have following entities:

- Book
- Book exemplar
- Borrowed by (a table between the entities Book exemplar and Student)
- Reserved for (a table between the entities Book exemplar and Student)
- Student

How would you represent the tasks I mentioned above in terms of triggers or procedures?...

View 5 Replies View Related

Trigger That Won't Allow A Member To Exceed Certain Number Of Loans At A Time Based On Their Category

Apr 14, 2015

I have an assignment to make a library. Right now I'm at the point of implementing business rules. One is that I need to create a trigger that won't allow a member to exceed a certain number of loans at a time, based on their category (student = 5, Teacher =10 and Researcher = 20).

View 1 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

The Number Of Requests For XXXServerXXXUser Has Exceeded The Maximum Number Allowed For A Single User

Oct 15, 2007



I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.


Reporting Services Error
--------------------------------------------------------------------------------

The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services


Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?

View 2 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

How To Change A Decimal Number To Percent Format Number?

Oct 8, 2006

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View 4 Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns

Jun 5, 2007

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

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

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

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

1 To Many Updates

Feb 5, 2004

I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).

The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.

I have a number of questions:

How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?

Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.

What is being passed? Can you pass an array or something to a stored procedure?

View 4 Replies View Related

Using In For Updates

Jun 19, 2007

As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...

View 3 Replies View Related

Run 3 Updates In One?

Sep 20, 2014

I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.

UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer]
WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));

View 3 Replies View Related

37 Updates In One

May 10, 2007

okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?

here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)


update t1 set books_music='' from mailorder t1 where books_music is null
update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null
update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null
update t1 set Computer='' from mailorder t1 where Computer is null
update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null




in FoxSlow (foxpro) i could just do something like this:


do while !eof()
replace books_music with '' for books_music=null
replace car_buff with '' for car_buff=null
skip
enddo

View 9 Replies View Related

MS Updates?

Feb 17, 2008

I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?

View 3 Replies View Related

2 Updates In The Same Querry!

Oct 29, 2007

Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes':
1)  "Update tblDept SET IsTop ='No'
2) "Update tblDept  SET IsTop = 'Yes'  WHERE id = 200 "
Cheers!

View 4 Replies View Related







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