Trigger For The Same Table !!!!! URGENT PLEASE
Sep 17, 2006
Hi .... This is my problem .....
I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:
In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?
I try this into de Trigger Code ....
DECLARE @FECHA_1_ANT AS datetime
DECLARE @FECHA_2_ANT AS datetime
DECLARE @FECHA_1_INS AS datetime
DECLARE @FECHA_2_INS AS datetime
Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID
But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....
Please help me !!!!!
Thanks
View 3 Replies
ADVERTISEMENT
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 6, 2007
Posted - 03/05/2007 : 01:58:00
--------------------------------------------------------------------------------
Hi pals,
I need one trigger for Auditing purpose.Currently we are using SQL Server 2005.
I have gone thru some articles on DDL trigggers but iam not able to get good info.
Frns, Need a trigger which can track all ddl's on all tables in the current database in an
audit table. It should include dropping tables,altering tables ... I should'nt allow
both these activities. The action performed must be tracked in the following table.
create table audit_ddl
(
login_user varchar(20),
login_time datetime,
table_name varchar(50),
action_performed varchar(200) -- Entire ALTER/DROP SQL stmt the user issued issued must be tracked here.
)
Can any senior DBA can help me out.
Thanks & Regards,
franky
View 4 Replies
View Related
Jul 12, 2007
Dear Sir,
Hello I Need To Know ABout Trigger
What Is The Trigger ,and i need to Make Example On the Trigger that
I Have Two Table,Employees, Department
i nedd to make Trigger That when i make insert into the Employees Table ,
The No-of Employee Column on the Department table will increase
With My Best Regard
Khalil.T.Hamad
View 7 Replies
View Related
Jul 12, 2007
Dear Sir,
Hello I Need To Know ABout Trigger
What Is The Trigger ,and i need to Make Example On the Trigger that
I Have Two Table,Employees, Department
i nedd to make Trigger That when i make insert into the Employees Table ,
The No-of Employee Column on the Department table will increase
With My Best Regard
Khalil.T.Hamad
View 6 Replies
View Related
Jul 12, 2007
Dear Sir,
Hello I Need To Know ABout Trigger
What Is The Trigger ,and i need to Make Example On the Trigger that
I Have Two Table,Employees, Department
i nedd to make Trigger That when i make insert into the Employees Table ,
The No-of Employee Column on the Department table will increase
With My Best Regard
Khalil.T.Hamad
View 1 Replies
View Related
Feb 21, 2002
Hi Guys,
I have a database replicated to another database in a remote server.
We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated).
Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this.
I appreciate your help
Thanks,
Jeyam
View 1 Replies
View Related
Jan 10, 2001
There are two table table A and table B , if something is updtaed in
table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....
Here is the Update trigger i wrote :
CREATE TRIGGER [updbacklog] ON [testbacklog]
FOR UPDATE
AS
Update test1backlog
Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber
, test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
, test1backlog.CustPONumber = inserted.CustPONumber
, test1backlog.Status = inserted.Status
, test1backlog.Comments = inserted.Comments
, test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
, test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
, test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
, test1backlog.WorkOrderType = inserted.WorkOrderType
, test1backlog.CustomerName = inserted.CustomerName
, test1backlog.Attn = inserted.Attn
, test1backlog.CustAddr1 = inserted.CustAddr1
, test1backlog.CustAddr2 = inserted.CustAddr2
, test1backlog.CustAddr3 = inserted.CustAddr3
, test1backlog.City = inserted.City
, test1backlog.State = inserted.State
, test1backlog.Postal = inserted.Postal
, test1backlog.Customer = inserted.Customer
, test1backlog.Address = inserted.Address
, test1backlog.ShipDate = inserted.ShipDate
, test1backlog.Carrier = inserted.Carrier
, test1backlog.Waybill = inserted.Waybill
, test1backlog.CanBeShipped = inserted.CanBeShipped
, test1backlog.PlannerCode = inserted.PlannerCode
, test1backlog.rowguid = inserted.rowguid
from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber
and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
and test1backlog.CustPONumber = inserted.CustPONumber
and test1backlog.Status = inserted.Status
and test1backlog.Comments = inserted.Comments
and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
and test1backlog.WorkOrderType = inserted.WorkOrderType
and test1backlog.CustomerName = inserted.CustomerName
and test1backlog.Attn = inserted.Attn
and test1backlog.CustAddr1 = inserted.CustAddr1
and test1backlog.CustAddr2 = inserted.CustAddr2
and test1backlog.CustAddr3 = inserted.CustAddr3
and test1backlog.City = inserted.City
and test1backlog.State = inserted.State
and test1backlog.Postal = inserted.Postal
and test1backlog.Customer = inserted.Customer
and test1backlog.Address = inserted.Address
and test1backlog.ShipDate = inserted.ShipDate
and test1backlog.Carrier = inserted.Carrier
and test1backlog.Waybill = inserted.Waybill
and test1backlog.CanBeShipped = inserted.CanBeShipped
and test1backlog.PlannerCode = inserted.PlannerCode
and test1backlog.rowguid = inserted.rowguid
Thanks a lot in advance.
View 6 Replies
View Related
Oct 4, 2000
Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
---Ram
View 1 Replies
View Related
Oct 4, 2000
Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.
---Ram
View 1 Replies
View Related
Aug 22, 2006
Hello Db experts,
Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
View 10 Replies
View Related
Feb 16, 2007
I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
View 9 Replies
View Related
Jul 23, 2005
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
View 1 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
Aug 3, 2005
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
View 4 Replies
View Related
May 24, 2007
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
May 19, 2014
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
View 4 Replies
View Related
Oct 13, 2006
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies
View Related
Jul 30, 2015
If the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 1 Replies
View Related
Jul 11, 2006
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
View 16 Replies
View Related
Jul 10, 2006
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
View 6 Replies
View Related
Mar 30, 2001
Hi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay
View 1 Replies
View Related
Aug 13, 2001
create Procedure AddNewStyle @table_nm varchar (20)
declare @string varchar (5000)
select @string='Update product
Set Product_End_dt = '12/31/05'
from ' + @table_nm + ' c inner join Booktable b
On (c.book_id = b.book_id).............
exec (@string)
--
I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help!
Reddy.
View 2 Replies
View Related
Feb 6, 2001
Hello,
I've a database in Production db which is 300 MB and one of its table has
948900 rows then how can I know how much size this particular table is of.
Thanks.
View 2 Replies
View Related
Feb 2, 2001
Someone, I don't know who, deleted an important table in my database. I've only been able to find restore routines on full databases. Is there any way to restore just a single table?
View 3 Replies
View Related
Oct 3, 2000
Hi,
How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..
View 1 Replies
View Related
Aug 6, 2007
Hi All
I want to convert table structure into xml in the following format.
Eg: <TABLE_NAME>
<COLUMN_NAME>
<IS_NULLABLE> </IS_NULLABLE>
<CONSTRAINT_TYPE> </CONSTRAINT_TYPE>
<CONSTRAINT_NAME> </CONSTRAINT_NAME>
<TABLE_CATALOG> </TABLE_CATALOG>
<LENGTH> </LENGTH>
</COLUMN_NAME>
</TABLE_NAME>
where TABLE_NAME is the table name in database and COLUMN_NAME is name of the column in above table and the remaning are the attributes to that column.
please suggesst me the sql query to get the following structre of any table in database,
Thanks
Rupal
View 1 Replies
View Related
Feb 9, 2006
Hi,
anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:
Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
Dim sqlCommand2 As New SqlCommand("", SqlConnection1)
Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))
Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)
Sub Page_Load(Source as Object, E as EventArgs)
sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"
sqlconnection1.Open()
............................
The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.
Ann123
View 4 Replies
View Related
