SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT TRIGGER
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KingCarlos
Yak Posting Veteran

Australia
74 Posts

Posted - 06/20/2012 :  21:07:48  Show Profile  Reply with Quote


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

India
52309 Posts

Posted - 06/20/2012 :  21:36:25  Show Profile  Reply with Quote
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/

Go to Top of Page

KingCarlos
Yak Posting Veteran

Australia
74 Posts

Posted - 06/20/2012 :  21:41:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/20/2012 :  21:45:04  Show Profile  Reply with Quote
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/

Go to Top of Page

KingCarlos
Yak Posting Veteran

Australia
74 Posts

Posted - 06/20/2012 :  22:27:31  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/20/2012 :  22:44:56  Show Profile  Reply with Quote
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/

Go to Top of Page

KingCarlos
Yak Posting Veteran

Australia
74 Posts

Posted - 06/20/2012 :  23:05:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/20/2012 :  23:09:52  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000