I am confused about triggers!!

[DeveloperJr]

I want to create an INSTAED OF trigger on a table to guarantee that only one contact is primary ‘P’ per CompanyID multiple others can be ‘S’ secondary.

My problem is not how to write the trigger, it is my confusion about a fact that I learned from nice folks here in this forum which is a trigger fires once per update even if we update multiple records. Now how can I handle the multiple update in my trigger code.

Is the code repeats itself for every record in the inserted/deleted virtual tables?

Imagine this scenario, my user try to update 2 records the first will not violate the rules mentioned above but the second will. Will the trigger succeed or fail?

Sorry if this question sound naïve and confusing, we all need to start some where.

 

[philhege]

Try this code. Run it in a personal database.

create table typetest (company varchar(24), contact varchar(24), contact_type char(1))

GO
CREATE TRIGGER typetest_uniqueprimary ON [dbo].[typetest]
FOR INSERT, UPDATE, DELETE
AS

if exists(select count(*) , company from typetest
WHERE contact_type = 'P'
group by company
HAVING count(*) > 1)
BEGIN
RAISERROR('Only one primary contact per company',16,1)
ROLLBACK TRANSACTION
END


GO

insert into typetest
(company, contact, contact_type)
SELECT 'Acme','Joe Smith','P'
UNION
SELECT 'Acme','Mary Jones','S'

INSERT INTO typetest
(company, contact, contact_type)
SELECT 'Acme','John Arbitrage','P'

UPDATE typetest
SET contact_type = 'P' where contact = 'Mary Jones' and company = 'Acme'

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[philhege]

PS I tried this with a multiple insert for primary contact against the company and it threw the error then, also. YMMV.