Is multiple action trigger possible

Hello

Is it possible to create a trigger with multiple actions?

I would like to create a trigger with INSERT, DELETE, UPDATE funtions but I have not been above to find a clear syntax example

Below I have created a statement of my trigger. Could somebody please confirm if the syntac in this multiple action trigger is ok.multple actions on a single trigger

Is this syntax correct?

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For INSERT, DELETE, UPDATE

AS

INSERT INTO Domain_CatA (DomainName)
SELECT DomainName FROM INSERTED

AS
DELETE FROM Domain_CatA (DomainName)
SELECT DomainName FROM DELETED

AS
UPDATE INTO Domain_CatA (DomainName)
SELECT DomainName FROM UPDATED

Thanks

Lynn

Why do you need triggers?

Is it possible to add the required actions to the insert, update and delete stored procedures?

|||

you have to split the insert/update from the delete trigger.

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For INSERT, UPDATE

AS

INSERT INTO Domain_CatA (DomainName)
SELECT DomainName FROM INSERTED

GO

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For DELETE

AS

DELETE FROM Domain_CatA (DomainName)
SELECT DomainName FROM DELETED

|||

Hello TATWORTH

Thanks for the reply.

Yes it is possible to use, insert, update and delete stored procedures, which is the current method I am using. However, as I have the same list of data on 26 categories tabes from A-Z, I thought it would be a more speedier and efficient method instead of using stored procedures.

Is there a good reason not to use triggers instead of stored procedures?

Thanks

Lynn

|||

Hello khtan

Thanks for the reply and detailed explanation.

Lynn

|||

>>Is there a good reason not to use triggers instead of stored procedures?

Stored procedures will give you better and more consistent performance. It has been known for triggers that function well during development, bring a production system to its knees. Stored procedures are better for another reason - all the components of an action is brought into one place thus they can be designed as a whole, reviewed as a whole and maintained as a whole.

I fully realise that many DBAs get excellant milage out of triggers, however in 10+ years of designing some 20 SQL server database, I have used triggers once and that was because I though a fellow DBA might enter some data by hand.

|||

Hello TATWORTH

Thanks for the information. I thought that a trigger would save me the task of entering the same stored procedure 26 times. Now repetitive stored procedures do not seem like too much of a chore.

Thanks

|||Just open up all the s.p. in SQL Management Studio and copy and paste ... copy and paste .. copy and paste!