Creating SQL 2008 After INSERT, DELETE, UPDATE Trigger

USE [dbRegistration]
GO
/****** Object:  Trigger [dbo].[Tr_CentralViva]    Script Date: 11/15/2011 09:37:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Tr_CentralViva]
   ON [dbo].[T_Term_CenteralViva]
   AFTER INSERT, UPDATE,Delete
AS BEGIN
	declare @CentViva decimal(18,2),
			@CentVivaOld decimal(18,2),
			@TermId varchar(50),
			@StudentId int,
			@CountI int,
			@CountD int

	select @CountI = COUNT(*) from inserted
	select @CountD = COUNT(*) from deleted

	if(@CountI > 0)
		select @CentViva = CenteralViva,@TermId = TermId, @StudentId = StudentId  from inserted

	else if(@CountD > 0)
		select @CentViva = CenteralViva,@TermId = TermId, @StudentId = StudentId  from deleted

	else
	begin
		SELECT @TermId = d.TermId,@StudentId = d.StudentId,
			@CentVivaOld = d.CenteralViva,
            @CentViva = i.CenteralViva
        FROM    inserted i
            INNER JOIN deleted d ON i.StudentId = d.StudentId
     end       

	update T_Term_Student_Result_Lab_Details
	set CenteralViva = @CentViva

	where TermId = @TermId
	and StudentId = @StudentId
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s