Trigger function help
-
I am trying to create a trigger function that when either an update or insert is done country will always be in upper case can anyone tell me why there is a red line under my table name saying it doesnt exist when i know it does im looking right at it on the side view
CREATE TRIGGER country_Insert_UpdateON dbo.cis111_LMccrorey_Customers
AFTER INSERT,UPDATE
AS
Update Customers
set Country = UPPER (Country)
where Country in (Select Country from inserted)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
GO
-
I think this is what you meant. Your code should be inside the BEGIN/END block, and it wasn't.
CREATE TRIGGER Customers_country_Insert_UpdateON Customers
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Customers
SET Country=UPPER(Country)
WHERE CustomerID IN (SELECT inserted.CustomerID FROM inserted);
END
Happy learning!
-
that code will update all rows, instead of just what was just inserted.
i'd do it this way instead, but i don't know the PK of the table
CREATE TRIGGER country_Insert_Update
ON dbo.cis111_LMccrorey_Customers
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update Customers
set Customers.Country = UPPER (INSERTED.Country)
FROM Customers
INNER JOIN INSERTED ON Customers.PKColumnName = INSERTED.PKColumnName
END
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (5/5/2015)
that code will update all rows, instead of just what was just inserted.Good point Lowell, without the restrictions this is a proper time bomb, down the line it will suddenly start to cause heavy locking, blocking and massive waits.
😎
-
Why bother with a TRIGGER on a two character column to UPPER the value. Why not just use a computed column AS (UPPER(Country))?
If you need indexing on it, make it PERSISTED.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply