Create Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create Trigger

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-02-20 : 09:43:17

Ok, I've got another one. Here’s the rundown…

I have two tables:

‘d_history’ and ‘a_master’

When a date is entered in ‘d_history.date_conferred’, I want that person’s ‘p_id’ to be entered in a new row in 'a_master'.

Any suggestions?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:45:02

[code]
CREATE TRIGGER ins_d_history
AFTER INSERT
AS
BEGIN
INSERT a_master (p_id, other columns...)
SELECT p_id,...
FROM INSERTED
END
[/code]

eevans
Starting Member

48 Posts

Posted - 2009-02-20 : 10:32:16

Thanks visakh16. You're the man!

I had to modify it slightly...

CREATE TRIGGER ins_d_history
ON d_history
AFTER UPDATE
AS
BEGIN
INSERT a_master (p_id)
SELECT p_id
FROM INSERTED
END

...but it seems to work ok.

eevans
Starting Member

48 Posts

Posted - 2009-02-20 : 10:46:14

One more question...

If p_id already exists in a_master, I am unable to update d_history.date_conferred. This is because the trigger tells it to create a duplicate of p_id but it can't because p_id is a primary key.

Is there a way to write the trigger statement in such a way that if p_id already exists in a_master, the trigger will be ignored when I update d_history.date_conferred.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:24:40

yup. do like this:-


CREATE TRIGGER ins_d_history
ON d_history
AFTER UPDATE
AS
BEGIN
INSERT a_master (p_id)
SELECT i.p_id
FROM INSERTED i

LEFT JOIN a_master a
ON a.p_id=i.p_id
WHERE a.p_id IS NULL

END

eevans
Starting Member

48 Posts

Posted - 2009-02-20 : 13:26:44

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:28:24

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources