CODEWITHSUNDEEP
sqlsql-server-2008triggerssql-update
user1598253
user1598253

Reputation: 11

SQL trigger help on UPDATE of row and column

I am having problems getting a SQL trigger to do what I want. I need to populate a field in a table when another field is updated. More specifically, when an employee ID is entered I need to generate Unicode to make a barcode work. I have written code to generate the unicode, I just need the trigger to function.

This is the skeleton of what I need:

CREATE TRIGGER Unicode
AFTER UPDATE of unique.id
ON table [dbo].table

FOR EACH ROW???
AS
BEGIN
     SELECT (unique.id2
            now I need to set unique.id2 to the unicode function that I wrote

I feel like I am on the wrong track with this.

Here is an image of the database I am working with (I did not create it) https://i.sstatic.net/L1zhp.png. Running SQL server 2008

When PersonalDataID[2] is updated (employee ID). I need to generate code for PersonalDataID[32].

Thank you for any help you can give!

Upvotes: 1

Views: 1349

Answers (3)

jos
jos

Reputation: 431

I don't think you need 'FOR EACH ROW???' :P

CREATE TRIGGER Unicode
ON table [dbo].table
AFTER UPDATE of unique.id
AS
Begin
  (SELECT unique.id2...)

Overall reference the answer above.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

To become proficient in SQL Server, the first thing you need to do is become comfortable with the fact that most operations work best when applied as a set. Thinking in terms of "for each row" will almost always lead you down the wrong path.

In this case, since an update can affect multiple rows, you need to deal with all of those rows at once. You can do this by using the inserted pseudo-table. I looked at your picture but your sample code is quite a mess and I have no idea how to correlate the two. So here is a quick example that you will have to tailor to your actual schema.

CREATE TRIGGER dbo.UpdateUnicodeColumn
ON dbo.table
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE t SET BarcodeColumn = (your calculation, a function, maybe?)
    FROM dbo.table AS t
    INNER JOIN inserted AS i
    ON t.key = i.key;
END
GO

Upvotes: 4

Jim
Jim

Reputation: 3510

Here's the syntax. Just replace somefield with whatever field you need entered and DestinationTable with the table you want to add the Unicode record to.

CREATE TRIGGER Unicode ON SomeTable
AFTER UPDATE 
AS

INSERT INTO DestinationTable
(somefield)
SELECT somefield FROM INSERTED
GO

Upvotes: 0

Related Questions