Khalil_ck2002



Dear Sir,

Hello I Need To Know ABout Trigger

What Is The Trigger ,and i need to Make Example On the Trigger that

I Have Two Table,Employees, Department

i nedd to make Trigger That when i make insert into the Employees Table ,

The No-of Employee Column on the Department table will increase

With My Best Regard

Khalil.T.Hamad




Re: Trigger(its very urgent)

WesleyB


Check out the Books Online, it contains excellent examples.

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com






Re: Trigger(its very urgent)

richbrownesq

You should look in Books Online at the CREATE TRIGGER statement.

Without knowing your exact DDL, i would guess you're looking at something like:

create trigger tr_e on employees

for insert

as

update department

set totalemps = totalemps + @@ROWCOUNT

from inserted

where inserted.deptid = department.deptid

GO

As an aside, i would question why you need to keep this running total in the first place. What if an employee changes department, or leaves the company completely You may need to consider your design and/or whether you need DELETE and UPDATE triggers too.


HTH!







Re: Trigger(its very urgent)

Mark D Allison

Something like this:

Code Snippet

CREATE TABLE [dbo].[departments](
[deptId] [int] NOT NULL,
[employeeCount] [int] NULL,
[description] [varchar](50) NULL,
CONSTRAINT [PK_departments] PRIMARY KEY CLUSTERED
(
[deptId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[employees](
[employeeId] [int] NOT NULL,
[firstname] [varchar](50) NULL,
[lastname] [varchar](50) NOT NULL,
[deptId] [int] NOT NULL,
CONSTRAINT [PK_employees] PRIMARY KEY CLUSTERED
(
[employeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[employees] WITH CHECK ADD CONSTRAINT [FK_employees_employees] FOREIGN KEY([deptId])
REFERENCES [dbo].[departments] ([deptId])
GO
ALTER TABLE [dbo].[employees] CHECK CONSTRAINT [FK_employees_employees]
GO

create trigger [dbo].[employees_i]
on [dbo].[employees]
after insert
as

declare @employeeCount int

select @employeeCount = count(*)
from employees e join inserted i
on e.deptid = i.deptid


update d
set d.employeeCount = @employeeCount
from departments d
join inserted i
on d.deptid = i.deptid
join
employees e
on e.deptid = d.deptid


You might want to amend the trigger to allow for deleted employees also. Hope this helps.

Mark.





Re: Trigger(its very urgent)

Khalil_ck2002

Thank You





Re: Trigger(its very urgent)

Khalil_ck2002

Thank You