Trigger To Create A Table On A Row Insertion
May 5, 2008
Hi there
I have a relatively noobish query and I am hoping to get a solution to it.
Heres the query in a nutshell.
I have a 'Type' table which has a 'TypeName' varchar attribute. So when I do a row insert into this Type table, I want a new table created with the value I insert into the 'TypeName' column as the table name.
For example If i insert 'xyz' into the 'Type' table for the 'TypeName' column. I wish for a trigger to fire which will create a table 'xyz' with some set attributes. I am really new to SQL Server and my preliminary googling left me disheartened with the results. So here I am.
I hope I was clear in the way I expressed my doubt and also that the people here might be able to help me out in this quest.
View 2 Replies
ADVERTISEMENT
Apr 14, 2008
hi,
i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences).
i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT.
hope this is possible .. or anyone have other suggestions?
thanks a lot!
View 4 Replies
View Related
Oct 6, 2006
In CLR integrated trigger:
If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees
I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]
How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.
Thank you.
View 1 Replies
View Related
Jul 7, 2015
It used to be so simple in SQL 2008.
With SSDT and VS2012, it seems impossible.Â
I created the project and I added a trigger
public
partialclassTrigger
{
   [Microsoft.SqlServer.Server.
SqlTrigger(Name =
"myPM10000", Target
= "[dbo].[PM10000]",
Event = "FOR UPDATE, INSERT")]
publicstaticvoidmyPM10000()
etc.
When I try to build the project it fails:
Error 1 SQL71561: Trigger: [dbo].[myPM10000] has an unresolved reference to object [dbo].[PM10000]. D:ProjectsVS2013myproject_CLRobjDebugmyproject_CLR.generated.sql 8 32 myproject_CLR
I have tried with and without adding a database reference. With the reference all I accomplish is lost time waiting for that monster link to the database to generate. There has to be a way to make this happen, right? I don't want to have to rewrite this in Transact SQL.
View 8 Replies
View Related
Feb 23, 2006
I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.
Here is what I did but It needs to be corrected:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
ALTER TRIGGER CreateTopicsTableTrigger
ON dbo.Forums
AFTER INSERT
AS
SET NOCOUNT OFF
DECLARE @myNewForum varchar
CAST(@@ROWCOUNT as varchar) /*Is it OK???*/
SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/
GO
CREATE Table @myNewForum /*Will this work some how???*/
( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,
DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
View 5 Replies
View Related
Apr 17, 2008
hi i have question
can sql server know when the row in table Saved CREATE TRIGGER date time on the ROW ?
add new field call "date_row_save" date+time
inside the the sql server
i need to know whan the row Saved
is it possible to do this in TRIGGER ?
TNX
View 3 Replies
View Related
Apr 21, 2008
hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?
Code Snippet
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename
Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')
Code Snippet
replace(@text,' ','')
create TRIGGER on update insert and not to damage the text in the all fields
TNX
View 21 Replies
View Related
Jun 19, 2008
Hi Friends,I have 3 labels Steet,City,Pincode and 3 textboxes related to the labels and one button as nae 'Address'I gave the data for Street:abc,City:xyz,Pincode:123 and have to insert into the table.I created one table in the database with table name Adreess and column address varchar(100)but after giving the values in the textboxes and clicked on the button its throwing the exception i.e System.Data.SqlClient.SqlException: The name "abcxyz123" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted.I wrote the code like following protected void Button1_Click(object sender, EventArgs e) { string street = txtStreetNo.Text; string city = txtCity.Text; string pincode = txtPincode.Text; string com = street + city+pincode; conn.Open(); SqlDataAdapter daInsert = new SqlDataAdapter("insert into Address values(" + com.ToString() + ")", conn); daInsert.SelectCommand.ExecuteNonQuery();--->here its giving the exception conn.Close(); Response.Write("the values are inserted"); }Please any one tell me am I did the code write or not if its not please give any suggetionsthanksGeeta
View 3 Replies
View Related
Apr 8, 2008
hello
i want to ask if the insertion of a record into a partion is slower than insertion it into a non partitioned table or not?
cuz sql has to decide to wich partion the record has to insert according to the partitioning key and is this decesion process is making insertion slower ?
View 2 Replies
View Related
Sep 9, 2013
I want XML data to be inserted int SQL table but could not figure out. #Currency is my table with assocaite columns and @XMLCurrency is a variable which holds XML string. How can I insert this XML data to my table.
Create table #Currency (CurrencyId int ,ISOCode nvarchar(10),ISONumbricCOde int,ISOName nvarchar(50), IsEnabledForMPV int default 0)
Declare @XMLCurrency nvarchar(max)
Set @XMLCurrency='<R><T><A>0</A><B>USD</B><C>840</C><D>US Dollar</D></T></R>'
Value 840 should insert into column ISONumbricCOde .
value USD should be insert into ISOCode column.
value 0 should insert into column CurrencyId.
values US Dollar should insert into column ISOName .
View 2 Replies
View Related
Jun 23, 2006
Hi,I have a table with 20.000.000 of tuples.I have been monitoring the performance of the insertion and updates,but not convince me at all.The table have 30 columns, what and 12 of it, are calcultated column.The test that i do was this:1 Insertion with all the columns and calculing the calcultated columnsin the insertion sentence.1 insertion and all the columns calculated in @vars..1 insertion with the basic fields, and 10 updates.And the result was that the last test was the most performant.What is your opinion?
View 4 Replies
View Related
Sep 20, 2007
Hi Experts,
I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
View 6 Replies
View Related
Oct 11, 2007
I want to insert data into Header and Details table simultaneously using SQLBulkCopy. Header table contains an Identity columns and Details table contains Foreign Key to this identity column in Header Table.I want to use DataTable as datasource in SQLBulkCopy.Can any body help on this
thanks
View 2 Replies
View Related
Oct 1, 2007
This is my code through which I insert the data into herm_Gen_Liab_Pre table.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[sample]
--@policyno varchar(9),-- FOR MOLD COVERAGE
--@formno varchar(6) -- FOR MOLD COVERAGE
as
TRUNCATE TABLE herm_Gen_Liab_Pre
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
DECLARE @genliabpre TABLE
( accmnth varchar(2),
accyear varchar (4),
treffmnth varchar (2),
treffyr varchar (4),
trexmnth varchar (2),
trexyr varchar (4),
statecode varchar (2),
typeofpolicy varchar(2),
exposure varchar(7),
annualstatementlineofbusinesscode varchar(3),
PremiumRecordId varchar (14),
Sublinecode varchar (3),
classcode varchar (5),
incemnth varchar (2),
inceyr varchar (4),
territorycode varchar(3)
--policyno varchar(9),-- for mold coverage
--formno varchar(6), -- for mold coverage
--moldcoverage varchar(1) -- mold coverage value
);
insert into @genliabpre (accmnth,accyear,treffmnth,treffyr,trexmnth,trexyr,
statecode,typeofpolicy,exposure,annualstatementlineofbusinesscode,PremiumRecordId,
Sublinecode,classcode,incemnth,inceyr,territorycode)
select datepart(m,p.EntryDate),
datepart(yy,p.EntryDate),
datepart(m,p.PremiumEffectiveDate),
datepart(yy,p.PremiumEffectiveDate),
datepart(m,p.PolicyExpirationDate),
datepart(yy,p.PolicyExpirationDate),
p.statecode,p.policytypecode,p.ExposureLimitAmt,s.lineofbusinesscode,
s.invoiceno,s.sublinecode,s.ClassCode,
datepart(m,p.PolicyOriginalEffectiveDate),
datepart(yy,p.PolicyOriginalEffectiveDate),s.TerritoryCode
from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s
on p.invoiceno = s.invoiceno
where
s.lineofbusinesscode in ('052','152','170','171','175','180')--,'192','194') -- general liability
AND P.STATECODE IN ('NY','NJ','PA','RI','GA')
and p.entrydate between '01/01/2004' and '12/31/2007'
order by p.entrydate
----------------------------INSERT MOLD COVERAGE--------------------------------
/*
SET @policyno = (select cast(pd.policyno as varchar(9))
from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf
on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from HERMITAGE.DBO.premiumdirect p join
HERMITAGE.DBO.premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
)
SET @FORMNO = (select PF.FORMNO
from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf
on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from HERMITAGE.DBO.premiumdirect p join
HERMITAGE.DBO.premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
)
*/
---------------------------------------------------------------------------------
insert into herm_Gen_Liab_Pre (
AccountingDate,
TransactionEffectiveDate,
TransactionExpirationDate,
StateCode,
typeofpolicy,
exposure,
AnnualStatementLineOfBusinessCode,
PremiumRecordId,
SublineCode,
ClassificationCode,
InceptionDate,
territorycode
)
select accmnth= case accmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end + + SUBSTRING(accyear,4,1),
treffmnth=case treffmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end + + substring(treffyr,3,2),
trexmnth = case trexmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end+ + substring(trexyr,3,3),statecode,
typeofpolicy,exposure,AnnualStatementLineOfBusinessCode,premiumrecordid,Sublinecode,
classcode,incemnth = case incemnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end+ + substring(inceyr,3,2),TerritoryCode
from @genliabpre
exec genliab_state
update herm_Gen_Liab_Pre
set territorycode = '999' where statecode = '31'
update herm_Gen_Liab_Pre
set TransactionTypecode ='1'
----------------------STATE EXCEPTION INDICATOR CODE-----------------------------
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode = '325' and classificationcode in ('99935','99936') and statecode = '20'
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode = '334'
and classificationcode in ('01412','01418','05113','05114','05118','05123','05124'
,'05125','05213','05223','05224','60010','60011','62003','63010','63011'
,'63012','63013','64500') and statecode = '20'
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode in ('345','347') and classificationcode = '80050' and statecode = '20'
select * from herm_Gen_Liab_Pre
The problem is there is a column in this table called riskidCoPaymentPercentageMoldDamageCoverageCode and I have to insert value '1' into that column based on this select query
select cast(pd.policyno as varchar(9)),pf.formno
from premiumdirect pd join policyforms pf on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from premiumdirect p join premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
order by pd.entrydate
How can i insert implement this. I tried temp variable, was successful partially, so need a better approach to it.
Chintan
View 6 Replies
View Related
Feb 1, 2006
how to restrict data insertion upto 50 MB in a table?
View 1 Replies
View Related
Oct 6, 2006
Hi,
This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.
This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.
My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?
How can i insert the value stored in a variable in a table from previous SQL Task.
Can anyone suggest some solution,links to try a workaround?
Thanks in advance.
Regards,
Aman
View 4 Replies
View Related
May 25, 2006
HI,
How to create package in SSIS by applying the business Logic like if the record already exist it should be and update else it should be an insert in the destination table. how to achive this funcality in SQL SERVER 2005 (Business Intelligence studion).
Thanks & Regards
Nagaraj.S
View 4 Replies
View Related
Nov 17, 2015
Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .
View 8 Replies
View Related
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
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies
View Related
May 2, 2008
Hi,
I found out that executing the procedure SP_INDEXOPTION and setting 'AllowRowLocks' to false i can prevent the sql server from locking rows in a table and 'AllowPageLocks' prevents from pages being locked. I need to preform same operation
in case of tables. I need to perform insertion operations concurrently and acquire required locks manually. Is there a way to stop sqlserver from acquiring locks on the table. I need to disable all the locks (row, page and table).
Thank you in advance.
View 9 Replies
View Related
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
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
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
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
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
Sep 14, 2004
I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.
I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.
I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.
CREATE TRIGGER NotifyDateFieldUpdates
ON RelocateeRemovalist
For INSERT, UPDATE, DELETE
AS
DECLARE @RemovalistNumber VARCHAR(200)
DECLARE @RelocateID INT
/*InspectionDate */
DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber
FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID)
WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL
OPEN getInsp
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT
RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON
(a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null
UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE()
WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
END
DEALLOCATE getInsp
GO
This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.
Regards and thanks
View 8 Replies
View Related
May 9, 2008
i do have my 'Product' TABLE IN DATABASE 'ABC'
Product TABLE OUTPUT
PRODUCT_CODE PRODUCT_TYPE PRODUCT_DESCPRODUCT_ID PRODUCT_GROUP_CODE
6001 computer NULL ENVD14
6002 keyboard NULL ENVD14
6003 mouse NULL ENVD14
6004 cables NULL ENVD14
6005 processor NULL ENVD14
AND 'Product_Mst' TABLE IN DATABASE 'XYZ'
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Now i want TO CREATE TRIGGER such that every updation in Product TABLE will UPDATE the appropriate record IN Product_Mst
FOR example IF i fire below query IN ABC Database
UPDATE Product
SET PRODUCT_DESC = 'Available' WHERE Product_Code = 6001
Then the OUTPUT OF the Product_Mst shoub be..
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6001 1 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Means i want to increment the version by 1 and Insert that records into Product_Mst Table at every updation.
I hope i am clear with my question.
Regards
Prashant Hirani
View 1 Replies
View Related
Jan 6, 2015
We have Tables like parent and Child Table.Like we have Child Table as Name AcademyContacts,In that we have Columns like
Guid(PK)Not Null,
AcademyId(FK), Not Null,
Name,Null
WorkPhone,Null
CellPhone,Null
Email Id,Null
Other.Null
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table.if the particular columns are empty while inserting also the data will get populate into the table.And.I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.
Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
View 4 Replies
View Related
Jul 20, 2005
Hi everybody,How can I Update a field from another table by Trigger? Can someone sendme the statment to do it?I have a table called Clients with fields : ID_Clients, ClientAnd Another called Doc with fields : ID_Doc, ID_Clients, ClientThese tables are in different databases and I would like to esure theintegrity by add a Trigger to update in Doc´s table the field Clienteverytime it´s changed in the Client´s table.Thanks for Attetion.Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 20, 2005
Hii have 2 Tablefirst one : Customerwith 4 Fields : cst_no,cst_name,total_Debit,tot_creditsecond one : Transactionwith 5 Fields : Trns_no,Trns_Date,cst_no,debit,creditMY QUESTION:HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS INCUSTOMER TABLE FROM Transaction TABLEThank you
View 4 Replies
View Related
Jan 10, 2008
Hi folks,
I have created a trigger that I can enter and works great from the sqlcmd terminal. When I try to submit the same query in a .NET project I get an error that "Create Trigger must be the first statement in a batch". The Create trigger is submitted by itself through the sqlcommand.executenonquery() method.
I am trying to create a database for a project but the only thing that I can't seem to get working is submitting this trigger.
Appreciate any help.
Dave
View 5 Replies
View Related
May 10, 2008
I am trying to create a trigger to help me get a the time duration but this is not working.
Code Snippet
CREATE TRIGGER Duratn
ON dbo.CONTACTS
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE dbo.CONTACTS
SET Duratn = (DATEDIFF(CallStartTime) - DATEDIFF(CallFinishTime))
My table is as follows:
No Caller CallStartTime CallFinishTime Duratn
10000 John 10/05/2008 18:13:00 10/05/2008 18:14:00 NULL
View 13 Replies
View Related
