New to triggeres - How? 1

[sparkbyte]

I am trying to make a trigger that sets the capitalization for names whenever an employee is added or changed.

Use MailroomTracking

Go

Alter Trigger trg_EmployeeProperName
	On tblEmployee

For Insert, Update

As
Begin

Update  dbo.tblEmployee
	Set		EmployeeFN = dbo.fn_ProperCase(EmployeeFN), 
			EmployeeMI = dbo.fn_ProperCase(EmployeeMI), 
			EmployeeLN = dbo.fn_ProperCase(EmployeeLN)
End

It is giving me an error aout to many rows being efffected by the Update.



Thanks

John Fuhrman

 

[gmmastros]

Within a trigger, there are 2 pseudo tables you have available to you. There is Inserted and Deleted. This tables will have identical structure as the table that the trigger is on. The inserted table will have a row in it for each row that is getting inserted or updated. The deleted table will have a row for each one that is getting deleted or updated.

For example:

If you insert 10 rows in to the table, the inserted table will have 10 rows and the deleted table will not have any rows.

If you update 10 rows, the inserted table will have 10 rows and the deleted table will have 10 rows. The data in the inserted table will have the new values and the data in the deleted table will have the old values.

If you delete 10 rows, the inserted table will have 0 rows and the deleted table will have 10 rows.

When you write a trigger, it is VERY important that you write it in a way that assumes multiple rows will be affected. For example, some people will write an insert trigger that assumes only one row will ever be inserted at the same time. Then, a year later, someone writes a query that inserts many rows and the trigger doesn't do what it is supposed to do. Write it right the first time, and you won't have to worry about it later.

Also (since I know SQLSister will probably read this), do NOT ever write a cursor in your trigger. If you do, I can pretty much guarantee that SQLSister will hunt you down! Triggers with cursors are notoriously slow, just don't do it.

Lastly, when you write a trigger, you should always have SET NOCOUNT ON as the first line.

Finally, if you want to change the data as it is being inserted and/or updated, you should join the real table with the inserted table so that ONLY the rows that need to be updated are actually updated. For example:

Use MailroomTracking

Go

Alter Trigger trg_EmployeeProperName
    On tblEmployee

For Insert, Update

As
Begin

  [!]Set Nocount On[/!]

  Update  dbo.tblEmployee
  Set     tblEmployee.EmployeeFN = dbo.fn_ProperCase(I.EmployeeFN),
          tblEmployee.EmployeeMI = dbo.fn_ProperCase(I.EmployeeMI),
          tblEmployee.EmployeeLN = dbo.fn_ProperCase(I.EmployeeLN)
  From    dbo.tblEmployee
          Inner Join Inserted As I
            On dbo.tblEmployee.PrimaryKeyColumn = I.PrimaryKeyColumn
End

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom