Trigger in Sql Server

Here is the sample of how to write trigger in sql server . The first one(InsertIntoPlayerProfile) will fire when record inserted into the  tbl_Player it will automatically insert the PlayerId into the tbl_PlayerProfile where table inserted is the temp table created when any table of the database is modified due to insert or update query.deleted is also the temp table created when any record deleted from the table.


CREATE  TRIGGER [dbo].[InsertIntoPlayerProfile]
ON [dbo].[tbl_Player]
AFTER INSERT
AS
Insert Into tbl_PlayerProfile(PlayerId)
SELECT i.PlayerId
FROM inserted AS i

Now for the Update 

CREATE TRIGGER [dbo].[UpdateIntoLogin]
ON [dbo].[tbl_Player]
AFTER Update
AS
   Update tbl_Login
Set Email=(SELECT i.Email
    FROM inserted AS i)
Where
UserName=(SELECT i.UserName
    FROM inserted AS i)

Now for Delete

CREATE TRIGGER [dbo].[DeleteFromPlayerProfile]
ON [dbo].[tbl_Player]
AFTER Delete
AS
--Delete Record from the tbl_PlayerProfile table for the related PlayerId
Delete from tbl_PlayerProfile
WHERE EXISTS
(
    SELECT d.PlayerId
    FROM deleted AS d
    WHERE tbl_PlayerProfile.PlayerId=d.PlayerId
) 

Return Type Stored Procedure in SQL

We can get a return from a stored procedure from another stored procedure , here sp_GetEmpTypeId  is use to return EmpTypeId From tbl_EmpType for an EmpType and sp_CreateEmployee which is calling  sp_GetEmpTypeId will insert empname  and the returned typeId into the EmpDetails table for a EmpName
-----------------------------------------------
Create procedure sp_GetEmpTypeId
(
    @EmpType nvarchar(150),
    @EmpTypeId int=0 output
)
As
If  @EmpType is not Null
Begin
    If Not Exists(Select EmpType From tbl_EmpType where EmpType=@EmpType)
        Begin
            Insert Into tbl_EmpType Values(@EmpType)
            Select @EmpTypeId=@@IDENTITY
        End
    Else
        Begin
            Select @EmpTypeId=(Select EmpTypeId From tbl_EmpType where EmpType=@EmpType)
    End
End

----------------
create proc sp_CreateEmployee
(
    @empname nvarchar(50),
    @EmpType  nvarchar(50)
)
As
Declare @typeId int
Set @typeId=0
---This will return the @typeId for the @EmpType
Exec sp_GetEmpTypeId @EmpType, @typeId output
----
Insert into EmpDetails
        (
            empname,
            typeId
        )
        values
        (
            @empname,
            @typeId
        )