How can i pass my .net application's Userid to the trigger? I have a audit trail trigger on myTable. I dont know how to pass the userid (not the sql server user) to the trigger when a user delete a record from the application(.NET Application).
The trigger saves the modifications on the table including the userid of one who does the changes.
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies View RelatedHi
I have to
implement table level loggin on a bunch of tables. The problem is that
I also have to log the user that made the changes
Obviously the
right way would be to run everything tru stored procs - but that is not
possible for me because there is a lot of existing functionality built
already - some direct sql and a super large number of sprocs.
So I wanted to go with a trigger based approach .However since the
changes are being made by a web app SQL has no idea who the actual web
application based user is.
I originally tried to solve the
problem by adding a LastUpdatedBy column to some of the tables and
forcing the functions to update that too - so that the trigger can read
that column but that has turned out to be very tedius and buggy.
So I want to try something else - so far it seems to be working but I
just want to make sure I am not messing up anything along the way.
There is a function is TSQL called APP_NAME() , this return the name of
the application that started the connection that the TSQL is running
in. This made scense during the desktop days when it would return the
actual .EXE name of the file that executed the changes.
Nowadys it's not so usefull any more becuase in a web based app all
changes are being done by aspnet_wp.exe so i don't know if that info is
usefull - as a matter of fact all access thru ado.net default to ".Net SqlClient Data Provider" .
So my thinking is like this - if I add 'Application Name= userid" to
the connection string before I open it - then I will have access to
that userid thruout any changes that usermakes via APP_NAME()
I realize that I may be messing up the connection pool a bit - but I
have relativley few (60) users who are on the system for long spans -
so each one will be able to reuse their own connectiions.
I just want to make sure before I go off on this that i am not messing things up badly
Thanks for reading this whole thing
I am new at replication and it has been going well, but have more of an information question that a problem. Basically I have transactional replication without updates. So i am pushing from the publisher to the subscriber only. I have added a table to the publisher side, and it is not replicating to the subscriber. Data pushes fine. I have read about reinitializing, adding article, etc. I do not know which is the way to go, I need it to happen automatically.
Which way and how? Any help is appreciated greatly.
Thanks
I have two tables (T1 and T2). In T1 I have a field FT1 that is aprimary key in T2 I have a field FT2 that is a foreign key linked toFT1. These fields have been populated with data. Lets say that in onerow of data I have in T1 under FT1 "my cell" as the data entry,similarly with T2 under FT2 I have 2 rows of data that also have "mycell" as the data entry. What is the best line of action is I wantedto change "my cell" to "my data"?
View 3 Replies View RelatedI want to find out count of same UserID used within 7 days for that month.
For example, in below scenario, UserID 458's 1st TTo is 8/26/2015 and it used again on 8/28/2015 which is less than 7 so result should be 1. (DateDiff between 1st TTo and 2nd Tfrom should be less than 7) This is for month of Aug.
ID TFrom TTo
9876 8/1/2015 8/7/2015
4140 8/21/2015 9/4/2015
458 8/23/2015 8/26/2015
458 8/28/2015 9/8/2015
Scenario 2,for UserId 458, TTo is 9/20 and it used again Tfrom on 9/20 so result should be 1Â
user ID TFrom T To
592 9/1/2015 9/24/2015
526 9/3/2015 9/11/2015
292 9/11/2015 9/25/2015
352 9/12/2015 9/24/2015
458 9/14/2015 9/20/2015
458 9/20/2015 10/2/2015
706 9/22/2015 10/6/2015
I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments
  Column ID      -- int, primary key
  Column sInstrument -- string
Table PTRS_INSTRUMENTATION:Â
  Column ID      -- int, primary key
  Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
  Column lMainItem  -- int, points to a row in table MAINITEMS via ID
  Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
  Â
Table MAINITEMS: List of chamber music works
  Column ID      -- int, primary key
  Column sItem    -- string
  Â
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
Is to Possible to Create a Triggers to capture Schema (alter table, Drop table) Changes only for certain tables.I don’t want schema change for entire database.
View 7 Replies View RelatedSQL express 2012. I am trying to case in the where part and having a syntax errors -Â This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ....
SQL express 2012
I am trying to case in the where part and having a syntax errors -Â this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ...
Suppose you have a simple DML "execute as..." trigger that looks like this:
CREATE TRIGGER dbo.myTrigger
 ON dbo.myTable
 WITH Execute As Owner
 AFTER INSERT
AS
[code]...
which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire? I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3. Â
If the declined date is filled in. Â Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but  i got an error message when trying this out.  Against the code Â
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. Â What do I need to do to get both my outputs working?Â
I must extract rows (with criteria) from a table with a few millions of rows in MS SQL 2000
Is there a way to COPY a table ?
if so is there a way to COPY it in a more selective way ?
something like :
COPY TABLE users AS usersCopy WHERE UserId > 500
thank you
I upgraded from 6.5 to 7.0 SP3. Now when I save (write) an invoice it takes about 10-12 seconds, at 6.5 it was 1-3 seconds. SQL Server and my Materials App are the only thing running on this box. This is the only area that has gotten slower everything else works great. I have 3 users saving invoices and about 15 people total using the system at one time. It's a compaq DL580 loaded with memory, database is 2,195MB in size. Same 6.5 client to access system as before. Should I rebuild/reindex the database? Is there something from the old 6.5 version I need to remove?? Thanks in advance!!!
View 1 Replies View RelatedWe are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
Im try to use SQL server 2000 and use Enterprise to cript a table
But my result is only table structure in the script (myfile.sql)
(something like "create table Tb1, column..") and not have any data in my table included.
how good it is if it can write some insert statement (depend on number of records existed).
Could I have any way to do it with MS SQLServer 2000?
Thank you much.
DongMT
In my site, when a user registers, I need to create rows in additional tables besides aspnet_Users. So, I need to be able to pass the generated userId guid to subsequent SqlCommands. I'm having a terrible time with this. What's the correct way to set up a SqlParameter so that it will accept a guid? I keep getting this error: "Conversion failed when converting from a character string to uniqueidentifier."
I've tried creating the parameter both with and without a SqlDbType.
cmd.Parameters.AddWithValue(paramName, guid);
and
SqlParameter p = new SqlParameter(paramName);p.SqlDbType = SqlDbType.Guid;cmd.Parameters.Add(p);
and I get the same error either way.
Driving me nuts! Any help appreciated.
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger   Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
Hi
I know this is going to sound embarassingly obvious, but i cannot find a quick solution.
I have some data, I display that data in a table. Simple so far.
I have a table footer, which I want to display the total number of rows returned.
For example
"Total Rows Returned ="
Now I know how to get the total rows returned:
CountRows("Dataset1")
However when I try to put the two together like this:
= "Total Rows Returned =" + CountRows("Dataset1")
I just get the whole thing outputted, and no total for totalrows...
Anyone know what I am doing wrong?
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
Hi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.
Kind Reggards
Dirceu
SQL Server 7.0 (Service Pack 1 )
A table is currently set up with Transactional Replication. A new field needs to be added to the table. The system needs to continue to be live. What is the best way to handle this kind of situation?
The database model cannot be frozen once replication is in place, and a fresh snapshot cannot be taken each time this occurs since this is very timely on large tables, and errors could occur with other tables replicating since there are foreign key constraint in the Target database.
I haven't been able to find a way to be able to continue replicating the old records which may have been in the log during the addition of the field, as well as the new records with the new field. I must be missing something, since the model must be able to be altered once replication is in place, without the shuting down of the entires system.
can any body provide me any idea
i need to know who had made changes to any particular stored procedure (when and from whose machine)
any thing regarding to any kind of tracking of stored procedure
In 2000 there was a server level setting you could change in EM, to allow direct modifications to systems catalogs.
in 2005 I would like to update some sids in sysusers tables, do I also need to find and set this option first
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.
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 RelatedThis 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.
Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.
Startdate =
CASE WHEN
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'
END
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.
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
I would like to make an after update trigger on a table (ARD TABLE) where depending on the criteria the trigger will either insert, update, or delete a row in the trigger table. Basically, in the trigger table there can only be rows where the column status is > 0. So, if the ARD TABLE has a row updated that used to have a status of 0 and now the status changed to 1 then insert it into the trigger table. If the ARD TABLE had a row that used to be 1 and now it's 0 then delete it from the trigger table. And if the ARD TABLE had a row that was 1 and now made any change to that row we need to update the corresponding row in the trigger table.
View 4 Replies View Related