Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia