I have been tinkering with triggers and have tried to build one that will format the names in two columns to UPPERCASE.
Trouble is it won't run and I don't know why, how can I accomplish this uppercase task with a trigger?
How can I create a trigger that obliges UPPERCASE of a field in thedatabase?thanks
View 3 Replies View RelatedHi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
In sql2005 how can I update a table in my db to give me all uppercase. I have never created a sp but would like to try to do this so can someone point me in the right direction. If a select/update statement is better then please give me suggestion that way also. Thanks in advance
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1445009
MELVIN
CALVIN
A
000142718
12/11/2007 6:44:00 AM
40
503
5831
mark
lama
A
000005831
12/11/2007 7:51:00 AM
41
503
01
8300
tito
fernandez
A
000008300
12/11/6:58:00 AM
41
503
01
2380
ANNABEL
MEZA
A
00000380
12/11/2007 8:06:00 AM
41
503
01
3697
armando
hernandez
A
000003697
12/11/2007 7:24:00 AM
41
503
01
5208
juan
guardiola
A
000005208
12/11/2007 7:29:00 AM
41
503
01
523614
JOHNNY
KELLEY
A
000143625
12/11/2007 8:00:00 AM
41
503
04
Hi,
I have a table where the values in the Description field are all upper case. I found a script that will change all the words to UPPERCASE in the first character then LOWERCASE for the remaining until the next space is found. It will also repeat this for all words in the field.
The part I need help with is having this repeat through the entire table. Heres the code and thanks - troy
declare @input varchar(20)
declare @position int
set @input = (SELECT Description FROM tbl_ItemMast where wallysku ='10071')
set @position = 1
SET @input = Upper(substring(@input,1,1))+ LOWER(substring(@input,2,len(@input)-1))
WHILE @position < DATALENGTH(@input)
Begin
set @position = charindex(' ',@input,@position+1)
if @position = 0
goto done
SET @input = REPLACE(@input,substring(@input,@position,2),Upper(substring(@input,@position,2)))
End
DONE:
PRINT @input
I have a table where inactive names are lower case and active names areuppercase. Note: Not my design.Anyways I want to select all names form this table where the name isuppercase. I see collate and ASCII pop up in searches but the examplesdon't seem usable in queries as much as they were for creating tablesand such.Thanks,Phil
View 5 Replies View RelatedHi,
What is the dowside of not using all uppercase for predicates and key words?
I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?
Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;
Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;
Thanks,
Philippe
Hi all!This is the problem:User enters student name, in the database, only the 1st letter is uppercase and the rest is in Lower case.So, I want to fix this so as it is not case sensitive, i.e. the user can enter a name and it will return the recordwhether they enter it in upper or lower case.My Code:CvtUpperCase.Text = UCase(Content.Text) //stores user's input
Select Case OptionChoice
Case "Student_FirstName" MyCommand = New SqlDataAdapter("select * from [qryStudentDetails] where [qryStudentDetails].[Student_FirstName] like '" & CvtUpperCase.Text & "'" , myConnection)
....Any ideas??
Hi
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Thanks in advance
I am always changing the Sql Query Keywords to UpperCase and formatting the T-sql.
Is there a option so that i can change the keywords to uppercase automatically in the sql 2005 management studio ?
How do most of you format the sql statements?
Please let me know it will save me and most of us like me alot of time.
Your help will be most appreciated.
Thanks in Advance
Savvy
In Query Editor I type statements like this:
"select * from ...."
Does Query Editor support a "macro" facility where I could, via keystorke, uppercase all t-sql keywords? (so it would look like SELECT * FROM...)
TIA,
barkingdog
P.S. You think I'm lazy? I knew a programmer who was so lazy that his password was one character long!
I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.
View 4 Replies View RelatedI know you are able to display data all uppercase or all lowercase, but how do you display it First letter capital rest lower. Like a First or Last name?
View 3 Replies View RelatedI would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies View RelatedThe states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
View 3 Replies View RelatedI am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
Thanks!
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.
The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.
Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.
Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?
Thank you
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
hii all,can any one help me to get this information.
i am using sql server 2000 and i want to know:
1- all names of servers
2- all database names (catalogs) at specific server and only which the user created .
3- user name and password for each server.
thanks.
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.
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
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies View RelatedWhen 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 RelatedSalve, 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 RelatedHi,
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,
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