logo
Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Options
Go to last post Go to first unread
pawel87  
#1 Posted : Thursday, August 26, 2010 5:28:33 PM(UTC)
Guest

Rank: Guest

Groups: Guest
Joined: 9/17/2008(UTC)
Posts: 66

Using triggers is very useful, when some tables in the database are in relation.
Let's imagine following situation:

We have 2 tables which are in relation.

1)Company
-Id
-Name

2)Employee
-Id
-FirstName
-LastName
-CompanyId (FK from 'Company' table)

Now, we want to delete one row from "Company" table, so we also need to delete all employees from "Employee" table who were employed in 'deleted company'.
Solution to do that is very simple - just create trigger which automatically delete all rows from "Employee" table which "CompanyId" is equal to deleted ID from "Company" table.

Sample code:
Code:

CREATE TRIGGER DeleteEmployeesFromCompany
ON Company
FOR DELETE
AS
@delId = SELECT Id FROM Deleted
BEGIN
DELETE FROM Employee WHERE CompanyId = @delId
END


After creating this trigger, when specified row from "Company" table is deleted, trigger automatically deletes all related rows from "Employee" table.


Generally, trigger is fired when an INSERT, UPDATE or DELETE occurs.

For more information about triggers please check following sites:

http://www.devguru.com/technologies/t-sql/7137.asp
http://netprogramminghelp.com/sql-server/t-sql-triggers/
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

pawel87  
#2 Posted : Thursday, August 26, 2010 6:34:04 PM(UTC)
Guest

Rank: Guest

Groups: Guest
Joined: 9/17/2008(UTC)
Posts: 66

Another example can be following:
We want to prevent situation when 2 users will have the same email address.
As a solution, we can use following trigger:

Code:

CREATE TRIGGER UniqueEmail
ON Person
FOR INSERT
AS
IF (SELECT COUNT(*) FROM Person WHERE Emailaddress = (SELECT Emailaddress FROM inserted)) > 1
BEGIN
	DELETE FROM Person WHERE Id = (SELECT Id FROM Inserted)
END


So, when we add new user which email address already exists in the database table 'Person', then trigger will delete this new inserted row
bart  
#3 Posted : Friday, August 27, 2010 11:57:43 AM(UTC)
bart

Rank: Administration

Reputation:

Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188
Man

Was thanked: 1 time(s) in 1 post(s)
Nice Posts,

For this Second example, this problem also exists on update because one can update Emailaddress.
but at this point a Delete is not acceptable so at that moment you need a rollback.
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.