INSERT 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

General SQL Server Forums

New to SQL Server Programming

INSERT TRIGGER

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-06-20 : 21:07:48

Can anyone assist with my trigger? I basically have an insert script that I want to automate with a trigger, but I cannot get the trigger to work? - any help greatly appreciated.

CREATE TRIGGER [dbo].[Trigger2] ON [dbo].[contacts]
insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 21:36:25

your trigger doesnt make much sense. As of now it inserts entire records from contacts sstisfying where condition each time you do a DML operation (i dont know what operation as you've not specified it in posted code above. it should be either insert,update or delete).
Also I think you should be using temporary tables inserted,deleted inside trigger to capture only rows affected by DML operation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-06-20 : 21:41:52

I managed to figure it out

USE Webinar_JJ;
GO
IF OBJECT_ID ('Trigger4','TR') IS NOT NULL
DROP TRIGGER Trigger4;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Trigger4 ON Contacts
AFTER UPDATE
AS
IF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts (nolock)
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
)
BEGIN
RETURN
END;
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
;
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 21:45:04

sorry still this looks a bit odd!


...
IF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated, 
contacts.dchanged, contacts.dchanged 
from contacts (nolock)
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
)

as it still looks for entire table not records that are affected by update alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-06-20 : 22:27:31

Hi there,
I was hoping my SQL trigger would see a record change and then insert a record into a new table, but as you can see it is not exactly working.
Any help appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 22:44:56

can you first explain your full scenario? for each update, what exactly you want to trigger to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-06-20 : 23:05:18

Sure, the trigger concept is quite simple.

When the contacts.iyesno1 field changes from 0 to 1 then I want to extract some of that data from the contacts table and insert it into the opt_out table.

Everything works except I have to keep running the trigger. Instead I want the trigger to sit there, see the change and then act.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 23:09:52

As per your explanation below is sufficient


USE Webinar_JJ;
GO
IF OBJECT_ID ('Trigger4','TR') IS NOT NULL
DROP TRIGGER Trigger4;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Trigger4 ON Contacts
AFTER UPDATE
AS
BEGIN
INSERT INTO opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select i.contactid, i.cfirstname, i.clastname, i.cemail1, i.cphone1, i.dcreated, i.dchanged, i.dchanged 
from inserted i
inner join deleted d
on i.contactid = d.contactid
where i.iyesno1 = '1' 
and d.iyesno1 = '0'
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources