November 22, 2017 at 8:28 pm
Hi,
I have an insert command saved to a table in a database "after insert" the trigger take the insert command and execute it. when the trigger execute it save the tweet in a second table on the in the same database and it work fine but the trigger in the second table does not work at all. following are the trigger and the table's:-
Command Insert
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
INSERT INTO [jbmolver].[dbo].[all_tweets] ([set_str],[name]) VALUES
("INSERT INTO [jbmolver].[dbo].[Twitter_Feeds] ([Twitter_ID],[Feed_ID],[Twitter_Date],[screen_name],[Author_Details],[Author_Email],[Author],[Sentiment],[sdate],[edate],[keyword],[type],[ncount],[latitude],[longitude],[radius],[lang],[friends_count],[followers_count],[following],[statuses_count],[location],[time_zone],[description],[in_reply_to_status_id],[in_reply_to_user_id],[in_reply_to_screen_name],[Rcounter],[address],[Feed_From_SM],[profile_image],[current_user_retweet],[withheld_scope],[retweet_count],[favourites_count],[url_type],[hashtags],[expanded_url]) VALUES ('933231091967778816','799796908478894080','2017-11-22 09:10:05',N'prettyraebae',N'Sis it s almost 2018 😭 https://twitter.com/babydrii_/status/933230970412683264', '', 'Rae', '1', '', '', '', 'photo', '', '', '', '', 'en', '461', '737', '', '12503', '', '', N'UGA 20 ❤️', '', '', '', '0', '$', '2', 'http://pbs.twimg.com/profile_images/867998936388313089/2Zdl5P2-_normal.jpg', '0', '0', '0', '27988', '', '', '')", 'Twitter_Feeds')
Table No. One:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[all_tweets](
[id] [int] IDENTITY(1,1) NOT NULL,
[set_str] [nvarchar](max) NULL,
[name] [nchar](30) NULL,
CONSTRAINT [PK_all_tweets] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Trigger on Table One:
/****** Object: Trigger [dbo].[exec_Update] Script Date: 11/23/2017 5:09:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[exec_Update] on [dbo].[all_tweets]
AFTER INSERT
AS
BEGIN
declare @id int = 0
set @id = ( select top 1 id from inserted )
exec [dbo].[execute_all_tweets] '[dbo].[all_tweets]','[set_str]',@id
END
Table Number Two
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Twitter_Feeds](
[id] [int] IDENTITY(1,1) NOT NULL,
[Twitter_ID] [bigint] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Twitter_ID] DEFAULT ((0)),
[Feed_ID] [nvarchar](50) NULL,
[Twitter_Feed] [varchar](200) NULL CONSTRAINT [DF__Twitter_F__Twitt__6E01572D] DEFAULT (''),
[Resource] [nchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_Resource] DEFAULT (''),
[Twitter_Date] [datetime] NULL,
[screen_name] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__scree__6FE99F9F] DEFAULT (''),
[friends_count] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__frien__70DDC3D8] DEFAULT (''),
[followers_count] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__follo__71D1E811] DEFAULT (''),
[following] [nvarchar](50) NULL,
[statuses_count] [nvarchar](50) NULL,
[description] [nvarchar](max) NULL CONSTRAINT [DF__Twitter_F__descr__72C60C4A] DEFAULT (' '),
[Author_Details] [nvarchar](max) NULL CONSTRAINT [DF_Twitter_Feeds_Author_Details] DEFAULT (''),
[Author_Email] [varchar](100) NULL CONSTRAINT [DF__Twitter_F__Autho__74AE54BC] DEFAULT (''),
[Author] [nvarchar](100) NULL CONSTRAINT [DF__Twitter_F__Autho__75A278F5] DEFAULT (''),
[Sentiment] [int] NULL CONSTRAINT [DF__Twitter_F__Senti__76969D2E] DEFAULT ((0)),
[sdate] [date] NULL,
[edate] [date] NULL,
[keyword] [nvarchar](250) NULL CONSTRAINT [DF__Twitter_F__keywo__778AC167] DEFAULT (' '),
[type] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_type] DEFAULT (' '),
[ncount] [int] NULL,
[latitude] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__latit__787EE5A0] DEFAULT (''),
[longitude] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__longi__797309D9] DEFAULT (''),
[radius] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_radius] DEFAULT ((0)),
[lang] [nvarchar](5) NULL,
[in_reply_to_status_id] [nvarchar](50) NOT NULL CONSTRAINT [DF_Twitter_Feeds_in_reply_to_status_id] DEFAULT ((0)),
[in_reply_to_user_id] [nvarchar](50) NOT NULL CONSTRAINT [DF_Twitter_Feeds_in_reply_to_user_id] DEFAULT ((0)),
[in_reply_to_screen_name] [nvarchar](50) NULL,
[Rcounter] [int] NULL CONSTRAINT [DF_Twitter_Feeds_Rcounter] DEFAULT ((0)),
[country] [nvarchar](100) NULL,
[State] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[address] [varchar](250) NOT NULL CONSTRAINT [DF_Twitter_Feeds_address_1] DEFAULT (''),
[location] [nvarchar](50) NULL,
[time_zone] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_time_zone] DEFAULT (' '),
[Feed_From_SM] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Feed_From_SM] DEFAULT ((1)),
[gender] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_gender] DEFAULT ((0)),
[Client_id] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Client_id] DEFAULT ((0)),
[image] [image] NULL,
[profile_image] [nvarchar](180) NULL,
[withheld_in_countries] [nchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_withheld_in_countries] DEFAULT (''),
[withheld_scope] [nchar](10) NULL CONSTRAINT [DF_Twitter_Feeds_withheld_scope] DEFAULT (''),
[retweet_count] [int] NULL CONSTRAINT [DF_Twitter_Feeds_retweet_count] DEFAULT ((0)),
[current_user_retweet] [int] NULL,
[favourites_count] [int] NULL,
[expanded_url] [nvarchar](max) NULL,
[url_type] [nchar](150) NULL,
[hashtags] [nchar](150) NULL,
CONSTRAINT [PK_Twitter_Feeds_2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Trigger On table number two
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[wordlist_splite_update] on [dbo].[Twitter_Feeds]
AFTER INSERT
AS
BEGIN
--set nocount on
DECLARE @MyString NVARCHAR(100),@TABLENAME NVARCHAR(50)='[dbo].[twitter_feeds]',@FIELD NVARCHAR(50)='[author_details]',@min int = 4
SET @MyString = ','
declare @Country nvarchar(80),@State nvarchar(50),@City nvarchar(50),@y int, @i int
declare @w1 Nvarchar(256),@w2 Nvarchar(256),@w3 Nvarchar(256),@w4 Nvarchar(256),@fld Nvarchar(256),@ID INT,@Twitter_Date datetime
set @fld = (select top 1 keyword from inserted)
set @Twitter_Date = (select top 1 Twitter_Date from inserted)
set @id = (select top 1 id from inserted)
SET TEXTSIZE 2147483647;
--exec [dbo].[top_word_list_by_location] '[twitter_feeds]','[author_details]', @id
IF OBJECT_ID('dbo.Cur1', 'U') IS NOT NULL
DROP TABLE dbo.Cur1;
IF OBJECT_ID('dbo.sometable', 'U') IS NOT NULL
DROP TABLE dbo.sometable;
--SET NOCOUNT ON
DECLARE @Words INT, @Pos INT, @x Int, @STR varchar(256),@screen_name nvarchar(100),@Author_Details nvarchar(280)
, @word varchar(256), @start int, @end int, @exitstart int
SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
if @id > 0
begin
exec('DECLARE Cur1 CURSOR FOR SELECT id, [screen_name],('+@FIELD+'),Twitter_Date FROM '+@TABLENAME +'where id='+@id )
end
else
begin
exec('DECLARE Cur1 CURSOR FOR SELECT id, [screen_name],('+@FIELD+'),Twitter_Date FROM '+@TABLENAME)
end
OPEN Cur1
fetch next from cur1 into @id,@screen_name,@str,@twitter_date
--insert into [dbo].[wordlist] ([word]) values (@id)
while @@fetch_status = 0
begin
select * into #temp from [dbo].[splitwords](@str)
set @y = (select count(*) from #temp)
set @i = 1
while @i <= @y
begin
--insert into [dbo].[wordlist] ([word]) values (@i)
set @word = (select [value] from #temp where id = @i)
set @word = (select [dbo].[udf_parsealphachars](@word))
if not exists (select * from dbo.dictionary with(nolock) where word= ltrim(rtrim(@word)))
begin
if not exists(select * from [dbo].[wordlist] with(nolock) where (word=ltrim(rtrim(@word)) and used_day = day(@twitter_date) and used_month = month(@twitter_date) and used_year = year(@twitter_date)) and (ltrim(rtrim([screen_name])) = ltrim(rtrim(@screen_name)) and nullif(word,' ') is not null))
begin
if len(ltrim(rtrim(isnull(@word,'')))) >=0 -- @min-2
begin
insert into [dbo].[wordlist] ([word],[used_day],[used_month],[used_year],[screen_name],[add_dt])
(select ltrim(rtrim(@word)),day(@twitter_date),month(@twitter_date),year(@twitter_date),@screen_name,@twitter_date where (len(isnull(@word,'')) > @min-2))
end ---- if len(ltrim(rtrim(isnull(@word,''))))
end --- if not exists [dbo].[wordlist]
else
begin
update wordlist set wordcount = wordcount + 1,[lastupdate] = getdate() where word = ltrim(rtrim(@word)) and (len(isnull(ltrim(rtrim(@word)),'')) > @min-2) and (ltrim(rtrim(@word)) <> '-1')
end --- else [dbo].[wordlist]
end --- if not exists [dbo].[wordlist]
set @i = @i + 1
end ---- end while dbo.dictionary
fetch next from cur1 into @id,@screen_name,@str,@twitter_date
end ---- end fetch next
close cur1
deallocate cur1
--set nocount off
--exec('select top '+ @top +' * from wordlist order by wordid desc')
if object_id('dbo.cur1', 'u') is not null
drop table dbo.cur1;
if object_id('dbo.#temp', 'u') is not null
drop table dbo.#temp;
--set nocount off
end
November 30, 2017 at 3:12 pm
Just a guess here, but on the database there is an option called "Nested Triggers Enabled". In your situation I believe this should be set to true. Please verify the condition of that flag.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply