Trigger Critique

May 21, 2004

Here is a trigger I wrote to check for duplicate taxid's in my producer table. Duplicate taxid's with an '' value are allowed. I'm green when it comes to trigger coding so would anyone care to review my code and reply if there's a more efficient way to implement the trigger or handle errors.

Thanks PLJ

create trigger dbo.tib_producer on dbo.producer for insert as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Check for duplicate tax_id if inserted is not null */
if update(tax_id)
begin
if exists(select tax_id
from producer
where tax_id in (select tax_id
from inserted)
and tax_id <> ''
group by tax_id having count(*)>1)
begin
select @errno = 50002,
@errmsg = 'Tax ID already Exists'
goto error
end

end
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end

View 3 Replies


ADVERTISEMENT

Please Critique My Work

Feb 23, 2007

Hi,

I would really appreciate any constructive criticism of my sql script (see end of message).

I have been working for a week to pull data from a database that has evolved over the years. It might have been designed to begin with, but it has had a lot of changes made on the fly, which makes pulling some data from it very hard.

One example is that they wanted Primary Service for a given client but this data was not stored in a base client table, but was instead on a table with multiple rows per client. To pick the row that referenced the correct Primary Service I had to:
1. Join the Client table with the view on client number and primary program number
2. From the records returned look at another column (proc_cde) in the view and pick the one that had a higher priority than the others. The precedence order of the proc_cdes was determined by a list my boss gave me. I used this forum to help me figure this part out.

Anyway … I finally got it done and it works … but just because it works doesn't mean it is well written. What I would like is if some of you could take a look at the sql and critique it for good and/or bad practices.

The design of the database is a given (and not my doing) so I am not looking for a critique of it.

An Example of what I am asking for would be:
I ended up using temp tables to collect some of the more complicated data so that I could write a very straightforward final query. Was this a good or bad thing and if bad why?

I really want to get good at sql and avoid developing any bad habits, so please critique my sql.

Also please ask me any questions that could help you evaluate the code.

Thanks,

Laurie

Note: the comments at the top were all the specs I got for creating this report (they came in an email).

/*detail- primary clincian/provider priemp_num joined to facemp Y
primary service (act,cm,opt), pripgm_num primary program,
select proc_cde from v_autsvc join svc
on proc_cde where pgm_num=pripgm_num
H0039 then T1017 t1016 then H0036
location of service, pgm_num of 90862 auth
date of auth v_autsvc.aut_dte
cons name/number: CLTCLT
auth date range v_autsvc.autbeg_dte v_autsvc.autend_dte
number of claims in the last fiscal year v_clmsvc, count(*)
primary psycchiatrist. cltdmo.pspemp_num join phy on phy_num


select clt_num, count(*) as num_claims from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num

tables: v_autsvc, pgm, cltdmo.population='mi adult'
proc_cde in ('90862','90801')
autend_Dte>'10/1/06'

I need a report showing all Adult MI consuemrs receiving psych services by agency- like the
aggregate and detail of who gets them at where-bridgeways, sr srv... including our clinic.

i would need in the detail- primary clincian/provider, primary service (act,cm,opt), location of service,
date of auth, cons name/number, auth date range/# of units and number of claims in the last fiscal year and
primary psycchiatrist. if xxx asks for something similar, let me know as we might be duplicating.

i know that xxx is going to be asking for a report that is for just our clinic that has more to do with
insurance type. if it would be easier, you could combine the 2 reports as long as we have the capacity
to sort and print by provider. thanks*/

/**********************************************************************************
Primary Service
***********************************************************************************/
-- select primary proc_cde for primary service Candidates
drop table #ConsumerProcCodes
select distinct a.clt_num,
a.proc_cde,
a.aut_dte,
a.autbeg_dte,
a.autend_dte
into #ConsumerProcCodes
from cltdmo d join v_autsvc a on a.clt_num = d.clt_num
and d.pripgm_num = a.pgm_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte


-- create table of ProcCde Precedence
drop table #ProcCde_Precedence
create table #ProcCde_Precedence
(proc_cde varchar(10),
Precedence int)

insert #ProcCde_Precedence
(proc_cde,Precedence)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100 union all
select 'H2015', 110 union all
select 'T1005', 120

-- assign precedence to proc_cde
drop table #ConsumerProcCodesWPrec
select distinct c.clt_num,
c.aut_dte,
c.autbeg_dte,
c.autend_dte,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
left(c.proc_cde,5) as proc_cde
into #ConsumerProcCodesWPrec
from #ConsumerProcCodes c left join #ProcCde_Precedence p on p.proc_cde = left(c.proc_cde,5)

-- select primary proc_cde for each consumer and get associated PrimaryService
drop table #PrimaryService
select distinct p1.clt_num,
p1.proc_cde,
p1.aut_dte,
p1.autbeg_dte,
p1.autend_dte,
s.acttyp_des as PrimaryService
into #PrimaryService
from #ConsumerProcCodesWPrec p1 join svc s on p1.proc_cde = s.proc_cde
where Precedence = (select min(Precedence)
from #ConsumerProcCodesWPrec p2
where p1.clt_num = p2.clt_num)

-- Find Consumers with more than one primary Proc_cde
/*
select clt_num, proc_cde, aut_dte, autbeg_dte, autend_dte
from #PrimaryService
where clt_num in (select clt_num
from #PrimaryService
group by clt_num
having sum(1) > 1)
*/
/**********************************************************************************
Primary Location
***********************************************************************************/
-- select pgm_num for Primary location Candidates
drop table #ConsumerLOS
select distinct a.clt_num,
a.pgm_num,
p.pgm_nme
into #ConsumerLOS
from v_autsvc a join pgm p on a.pgm_num = p.pgm_num
where a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde = '90862'

-- create table of pgm_num for Location of Services Precedence
drop table #LOS_Precedence
create table #LOS_Precedence
(pgm_num int,
Precedence int)

insert #LOS_Precedence
(pgm_num,Precedence)
select 5600, 10 union all
select 6200, 20 union all
select 6000, 30 union all
select 1611, 40 union all
select 1612, 50 union all
select 1601, 60

-- assign precedence to pgm_num
drop table #ConsumerLOSwPrecedence
select distinct c.clt_num,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
c.pgm_num
into #ConsumerLOSwPrecedence
from #ConsumerLOS c left join #LOS_Precedence p on p.pgm_num = c.pgm_num


-- select pgm_num for primary location of Services for each consumer
drop table #PrimaryLOS
select distinct p1.clt_num,
case n.NumRows
when 1 then cast(p1.pgm_num as varchar(10))
else cast(p1.pgm_num as varchar(10)) + '*'
end as LocationOfService
into #PrimaryLOS
from #ConsumerLOSwPrecedence p1 join (select clt_num,count(*) as NumRows
from #ConsumerLOSwPrecedence
group by clt_num) n on p1.clt_num = n.clt_num
where Precedence = (select min(Precedence)
from #ConsumerLOSwPrecedence p2
where p1.clt_num = p2.clt_num)



-- Find Consumers with more than one primary pgm_cde for Location of Services.
/*select distinct clt_num from #PrimaryLOS
select * from #PrimaryLOS
where clt_num in (select clt_num
from #PrimaryLOS
group by clt_num
having sum(1) > 1)*/

/**********************************************************************************
Put it all together
***********************************************************************************/
drop table #FinalResults
select distinct c.clt_num,
c.fst_nme + ' ' + c.lst_nme as ConsumerName,
f.fst_nme + ' ' + f.lst_nme as PrimaryClinician,
p.fst_nme + ' ' + p.lst_nme as PrimaryPsychiatrist,
ps.PrimaryService,
COALESCE(n.num_claims,0) as num_claims,
los.LocationOfService,
ps.aut_dte,
ps.autbeg_dte,
ps.autend_dte
into #FinalResults
from cltctl c join cltdmo d on c.clt_num = d.clt_num and d.population = 'mi adult'
join v_autsvc a on c.clt_num = a.clt_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde in ('90862','90801')
join facemp f on d.priemp_num = f.facemp_num
left join phy p on d.pspemp_num = p.phy_num
left join #PrimaryService ps on ps.clt_num = c.clt_num
left join (select clt_num, count(*) as num_claims
from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num) n on c.clt_num = n.clt_num
left join #PrimaryLOS los on c.clt_num = los.clt_num
order by c.clt_num

select * from #FinalResults
/*
select * from #FinalResults
where PrimaryService is null

select * from #FinalResults
where LocationOfService is null

select * from #FinalResults
where PrimaryPsychiatrist is null

select * from #FinalResults
where PrimaryClinician is null

select * from #FinalResults
where num_claims = 0

*/

edited to make word wrap work

View 5 Replies View Related

Query Critique

Jul 23, 2005

I just finished a new query where I summarized detail information. I'mwondering if I did this really awkwardly or is this a common way towrite SQL? I've cross referenced the end results and the data seemsconsistant, so I am happy with the results.TIASELECTSESSION_ID,CAMPUS_ID,SUM(STUDENT_COUNT) AS STUDENT_COUNT,SUM(NEW_STUDENT) AS NEW_STUDENT_COUNTFROM (SELECTSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_ID,NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_STARTING_SESSION_IDTHEN (1) ELSE (0) ENDFROM (selectSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_IDFROM(selectSESSION_ID,STUDENT_ID,CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROMD_BI_STUDENT WHERE A.STUDENT_SKEY=D_BI_STUDENT.STUDENT_SKEY) ,STUDENT_COUNT = DAY0_CLASS_COUNT,(select student_starting_session_id fromf_bi_student_statistics where A.student_id =f_bi_student_statistics.student_id) as 'STUDENT_STARTING_SESSION_ID'from f_bi_registration_tracking_summary A) AS XWHERE STUDENT_COUNT > 0GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,STUDENT_STARTING_SESSION_ID) AS Y) AS ZGROUP BY SESSION_ID, CAMPUS_ID

View 3 Replies View Related

Can Someone Critique My Sql Relationship Structure?

Jun 6, 2008

I was building out my db and then ran into a problem updating a primary key value. I then started to doubt whether or not I even did this right. To simplify, I"m going to just create a smaller similar scenario and I just want to know if its right
 Lets say I have three tables
Order Tableordernum (PK)orderdateorderamt
Phone TablePhonenumber (PK)ordernum (FK)NumbertypeInternet TableCircuitID (PK)Phonenumber(PK)IPSMGW
now, Instead of creating a seperate column for just an incrementing numeric value as a PK i used the order number, phone number, and circuit id's as the Primary keys since every single value will be unique. Plus, I also did it to prevent duplicates. Now the problem I'm having is, when I do Update db set [phonenumber] = @phonenumber, ordernum = @ordernum, numbertype = @numbertype where phonenumber = @phonenumber as wrong as that looks.
It looks wrong to me.. problably why it doesn't work. How do you change the values of primary keys.. or should I have created a column called ID with incrementing numbers. How would I prevent duplicates then?

View 5 Replies View Related

1st Attempt, Designing, Would Like Somebody To Review And Critique It

Jan 23, 2001

Hello

This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.

Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.

take care,
~Iris~
:)

View 1 Replies View Related

Expert's Critique On Package Design

Aug 31, 2007

All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.

I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.

Thanks!

View 7 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Generic Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)

Dec 5, 2006

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.

The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}


//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

View 16 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Oct 30, 2007

Table 1





First_Name

Middle_Name

Surname


John

Ian

Lennon


Mike

Buffalo

Tyson


Tom

Finney

Jones

Table 2




ID

F

M

S

DOB


1

Athony

Harold

Wilson

24/4/67


2

Margaret

Betty

Thathcer

1/1/1808


3

John

Ian

Lennon

2/2/1979


4

Mike

Buffalo

Tyson

3/4/04


5

Tom

Finney

Jones

1/1/2000


I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.

View 17 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Feb 5, 2008

A





ID

Name


1

Joe


2

Fred


3

Ian


4

Bill


B





ID


1


4

I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?

I believe the trigger should look something like this:

create trigger test_trigger
on a
for insert
as
insert into b(ID)

select i.id
from inserted i
where
--specific USER

View 9 Replies View Related

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

Mar 18, 2008

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

View 3 Replies View Related

Modifing The Row That Invokes A Trigger From Within That Trigger

Jul 23, 2005

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

View 4 Replies View Related

Disabilitazione Trigger [DISABLE TRIGGER]

Jul 20, 2005

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

View 4 Replies View Related

Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies View Related

Drop Trigger With A Variable Trigger Name

Sep 20, 2007



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

I have the following Trigger

the error is at

DROP TRIGGER @DeleteTrigger

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

thanks in advance

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

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

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

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

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

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @DeleteTrigger as nvarchar(max)

select @DeleteTrigger = TableName FROM DELETED



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

DROP TRIGGER @DeleteTrigger

GO

END

GO

View 7 Replies View Related

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

Jan 23, 2008



Hi,

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

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

Any thoughts?


Here is my t-sql


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

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

where i.req_guid = d.req_guid

and i.req_fname <> d.req_fname



Thanks,
leo

View 7 Replies View Related

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

Mar 3, 2008

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

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

View 1 Replies View Related

Sp Name In Trigger

Jun 17, 2006

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

View 1 Replies View Related

SQL Trigger .. Please Help...

Jul 26, 2006

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

View 6 Replies View Related

Trigger Ignored

Feb 4, 2007

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

View 6 Replies View Related

SQL Trigger

Mar 11, 2007

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

View 4 Replies View Related

About Trigger

Mar 17, 2007

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

View 1 Replies View Related

For Each Row Trigger

Aug 14, 2007

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

View 8 Replies View Related

Trigger && Asp.net

Aug 28, 2007

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

View 2 Replies View Related

Trigger

Nov 28, 2007

hi .... I am going to use trigger first time.
my secnerio is that I want a common column in five tables on the insertion of a row in a particular table..
question is simple ..... can I do that????????????????????
thanks in advance.

View 1 Replies View Related

MS SQL Trigger

May 28, 2008

 
Hello Experts,

Currently I have 3 tables; Persons, Student and Teacher. Persons is the parent table of Student and Teacher and they are linked by the Person_Id attribute. For example, if Person_Id 1 exist in Student table, it cannot be inserted anymore into Teacher table. How do I write a trigger in MS SQL to implement this business rule? Any experts can shed some light? Thanks!

View 17 Replies View Related

Help With Trigger

Feb 12, 2004

I am trying to write a trigger that alows me to re-arrange the order of items. For instance, if I insert a record with a SF_NEWS_Order of "1" then all records with an order of "1" or more would increase by 1. This allows me to insert a record in the list of priorities. For some reason, the trigger is not working. If I have reords with SF_NEWS_Order values as follows:

1
2
3

I end up with this:

1
1
2
3

Can someone help with this? The code of the existing trigger is below:

CREATE TRIGGER NEWS_PRIORITY ON [dbo].[TBL_SF_NEWS]
FOR INSERT, UPDATE
AS
Declare @Order_Code int
Declare @New_ID int
Declare @Exist_Record int

Set @New_ID = ( Select SF_NEWS_ID from Inserted )
Set @Order_Code = ( Select SF_NEWS_Order from Inserted )
Set @Exist_Record = ( Select SF_NEWS_ID from [dbo].[TBL_SF_NEWS] Where SF_NEWS_Order = @Order_Code and SF_NEWS_ID <> @New_ID )

If (@Exist_Record <> Null ) Begin
Update [dbo].[TBL_SF_News] Set SF_NEWS_Order = (@Order_Code + 1) Where SF_NEWS_ID = @Exist_Record
End

View 6 Replies View Related

Trigger

May 6, 2004

I have trigger firing during an update event. How can i fix the loop so this error will disappear..

Server: Msg 512, Level 16, State 1, Procedure TrigAssignImpTaskNew, Line 18
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.






SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER procedure TrigAssignImpTaskNew
@REIDint,
@RRID varchar(20)



AS
DECLARE @intREID varchar(20)
DECLARE @intIMID varchar(20)
DECLARE @intAPID varchar(20)
DECLARE @intCounter integer
Declare @RetValint

select @intCounter = count(*) from RequestRecords where REID = @REID
select @intAPID = (select APID from RequestRecords where reid = @REID and ITID = null and RRSTatus = 'PE')
select @intIMID = (select IMID from Applications_ImplementationGroup where apid = @intAPID)

while @intCounter <> 0
begin
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@intIMID,
'0',
GetDate()
)
SET @RetVal = @@IDENTITY


end

Update RequestRecords
set ITID = @RetVal,
RRStatus = 'PR'
where RRID = @RRID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 1 Replies View Related

Row ID Trigger

May 27, 2004

Anyone knows how to make a good trigger to create a row id on a table every time a new row is inserted? This is to maintain row id sequence by not setting the row id field as an auto identity which may be in broken sequence after a while.
Thanks.

View 9 Replies View Related

Need Help With Trigger

Jul 31, 2004

I've looked all over the internet for articles on Triggers, but none of them do exactly what I need. Could someone please see if this trigger would work? CREATE TRIGGER DuplicateEmployeeTrigger
ON Employees
BEFORE INSERT
AS
IF EXISTS (SELECT * FROM Employees e, INSERTED i WHERE e.Forename = i.Forename AND e.Surname = i.Surname)
ROLLBACK TRANSACTION
GO

View 3 Replies View Related

Trigger

Jan 6, 2005

I everyone,

I need to write a update trigger. I have a table which will keep track of the dates when the reocrds were changed. The way my application is designed I cannot update through my application, as on many occasions this will be done from query analizer.

I have written the trigger but it is actually editing all the values with new date where as it should only edit the value of the column for which the records were changed.

Thanks in advance

View 2 Replies View Related

Is A Trigger What I Need? Anybody?

Feb 28, 2006

Can I implement a trigger in this stored procedure to get the itemSell and multiply it by the newQuantity and update the lineTotal column? Is it possible to have both in here? If so how? I've been reading up on them and it seems like what I need to do I just can't seem to get it going.
CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO

View 1 Replies View Related







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